disable the range of cells on another sheet ( say Sheet2) based on the value in the current sheet(say Sheet1)

Question

I'm trying to disable the range of cells on another sheet ( say Sheet4) based on the value in the current sheet(say Sheet1)

I have tried to disable the values in the sheet4("M4,M6,M8,M10,M12") based on the values in sheet1("C5") by using worksheet change function.

With Sheet1 I have used the below code and getting the error message

Subscript Out of Range

Private Sub Worksheet_Change(ByVal Target As Range)

        Dim myRng As Range    
        Dim Cancel As Boolean

        If Not Intersect(Target, Me.Range("C5")) Is Nothing Then
            Set myRng = ThisWorkbook.Worksheets("Sheet4").Range("M4,M6,M8,M10,M12")
            Me.Unprotect Password:=SHEET_PASSWORD

            If Me.Range("C5").Value = "Yes" Then
                myRng.Locked = False
            Else
                myRng.Locked = True
                ThisWorkbook.Worksheets("Sheet4").Range("M4,M6,M8,M10,M12") = ""
            End If
            Me.Protect Password:=SHEET_PASSWORD
        End If

End Sub

If I choose "Yes" in the cell value C5 in Sheet1, the cell values (M4,M6,M8,M10,M12) in sheet4 should be enabled. If i choose "No" in the cell value C5 in Sheet1, the cell values (M4,M6,M8,M10,M12) in sheet4 should be disabled.

Please be consistent in what you talk about (you talk about Sheet1 and Sheet2 in your text but you have Sheet4 in your code (reading minimal reproducible example might help). Also ChangeEventFlag is not defined and this code cannot run because an End If is missing. Please edit and improve your question. Also tell in which line you get errors and which. And actually you didn't ask a question. — Pᴇʜ
What is ChangeEventFlag? You did not close the first If with an End If — z32a7ul

Mikku Mikku · Accepted Answer · 2019-08-05T08:05:51

I think This is what you are looking For.

Private Sub Worksheet_Change(ByVal Target As Range)

    If ChangeEventFlag = 0 Then
        Dim myRng As Range
        Dim Cancel As Boolean

        If Not Intersect(Target, Me.Range("C5")) Is Nothing Then
            With ThisWorkbook.Worksheets("Sheet4")

            .Unprotect Password:="password"
            .Cells.Locked = False

            Set myRng = .Range("M4,M6,M8,M10,M12")

            If Me.Range("C5").Value = "Yes" Then
                myRng.Locked = False
            Else
                myRng.Locked = True
                myRng.Value  = ""
            End If
                .Protect Password:="password"
            End With

        End If
    End If

End Sub

Note:

Not sure about the use of ChangeEventFlag
Change the Password, I used password for testing

Use:

Whichever sheet you will paste this code on will Lock Range("M4,M6,M8,M10,M12") of Sheet4 if it's C5 is No(Anything Other Than Yes Actually) and will unlock it if C5 is Yes

disable the range of cells on another sheet ( say Sheet2) based on the value in the current sheet(say Sheet1)

1 Answers