Trying to lock cells in one worksheet depending on value in another worksheet cell

[ShutterAce]

Hello,

Any ideas as to why this will not run. I've been playing with it all day and can't figure out why it stops after the If statement is evaluated.

I'd bet money it's something minute I'm overlooking but I can't find it. I have changed everything I can think of in the two statements regarding the locking of the cells with no change in behavior.

Private Sub Worksheet_Change(ByVal Target As Range)
    If ActiveWorkbook.Sheets("Measure_Cut").Range("E3").Text = "0" Then
        ActiveWorkbook.Sheets("Notching").Range("E16:E18").Locked = False
    Else
        ActiveWorkbook.Sheets("Notching").Range("E16:E18").Locked = True
    End If
End Sub

Thank you!

 

[Jason Barnes]

Assuming the Worksheet_change is in Measure_sheet.

Take the 0 out and try just ""
Or Change .text to .value

Jason's

 

[mikerickson]

Is the sheet Notching protected?

 

[ShutterAce]

Hi Jason

No joy. If I change it to "Value" it doesn't seem to run. If I use "Text" at least the debugger complains.

Assuming the Worksheet_change is in Measure_sheet.

Take the 0 out and try just ""
Or Change .text to .value

Jason's

 

[ShutterAce]

Hi Mike,

Yes, I have tried it both ways. It makes no difference.

Is the sheet Notching protected?

 

[mikerickson]

The .Locked property of a cell describes what happens when the sheet is protected. To prevent the user from changing a cell both the cell must be locked AND the sheet must be protected.

 

[ShutterAce]

I knew that but I tried it both ways anyway. I have tried so many iterations I was grasping at straws.

The only other thing I can think of is that Measure_Cut!E3 is a drop down list. Would that make any difference? I think not but I have been wrong once or twice.

The .Locked property of a cell describes what happens when the sheet is protected. To prevent the user from changing a cell both the cell must be locked AND the sheet must be protected.

 

[mikerickson]

If you put this in Measure_Cut's code module, (and don't protect Notching with a password), this should work

Private Sub Worksheet_Change(ByVal Target As Range)
    With ThisWorkbook.Sheet("Notching")
        .UnProtect
        .Range("E16:E18").Locked = (Me.Range("E3").Value <> 0)
        .Protect
    End If
End Sub

 

[Jason Barnes]

I checked this code below and has no problems.
It has to go in Measure_Cut sheet code.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.ScreenUpdating = False
    If Worksheets("Measure_Cut").Range("E3").Text = "0" Then 'Change 0 if needed
        Worksheets("Notching").Select
        ActiveSheet.Unprotect Password:=1234 'Change 1234 to your password
        Worksheets("Notching").Range("E16:E18").Locked = False
        ActiveSheet.Protect Password:=1234 'Change 1234 to your password
        Worksheets("Measure_Cut").Select
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    Else
        Worksheets("Notching").Select
        ActiveSheet.Unprotect Password:=1234 'Change 1234 to your password
        Worksheets("Notching").Range("E16:E18").Locked = True
        ActiveSheet.Protect Password:=1234 'Change 1234 to your password
        Worksheets("Measure_Cut").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
    End If
End Sub

 

[ShutterAce]

Still no joy.

If you put this in Measure_Cut's code module, (and don't protect Notching with a password), this should work

Private Sub Worksheet_Change(ByVal Target As Range)
    With ThisWorkbook.Sheet("Notching")
        .UnProtect
        .Range("E16:E18").Locked = (Me.Range("E3").Value <> 0)
        .Protect
    End If
End Sub