Forum Discussion
Using protected cells
In the first place, ActiveSheet.Range("C2") will never be Nothing - it is an object with lots of properties. So the code below won't be executed.
In the second place, locking cells only works if the sheet as a whole is protected. The code will have to unprotect the sheet, lock or unlock some cells, then protect the sheet again. You can use
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("C2"), Target) Is Nothing Then Me.Unprotect Range("B43:J49").Locked = (UCase(Range("C2").Value) = "X") Me.Protect End If End SubSee the small sample workbook attached to this reply (you'll have to allow macros).
You should save the workbook as a macro-enabled workbook (.xlsm) and make sure that you allow macros when you open it.
maddyc The code should be in the worksheet module. You can activate this module by right-clicking the sheet tab in Excel and selecting View Code from the context menu.
- Private Sub Worksheet_Change(ByVal Target As Range)
If Not ActiveSheet.Range("C2") Is Nothing Then
If ActiveSheet.Range("C2").Text = "X" Then
ActiveSheet.Range("B43:J49").Locked = True
Else
ActiveSheet.Range(Cells("B43:J49")).Locked = False
End If
End If
End Sub
This is the code I am using now - and I stopped getting errors but it is not locking the cells I am asking it to. Does anyone have any advice.In the first place, ActiveSheet.Range("C2") will never be Nothing - it is an object with lots of properties. So the code below won't be executed.
In the second place, locking cells only works if the sheet as a whole is protected. The code will have to unprotect the sheet, lock or unlock some cells, then protect the sheet again. You can use
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("C2"), Target) Is Nothing Then Me.Unprotect Range("B43:J49").Locked = (UCase(Range("C2").Value) = "X") Me.Protect End If End SubSee the small sample workbook attached to this reply (you'll have to allow macros).