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).
In your first post, you mentioned A2, now you mention C2. Has this changed? Also: ActiveSheet.Cells(C2) should be Range("C2")
ActiveSheet.Range(Cells(B43), Cells(J49)) should be either
Range(Cells("B43:J49")
if you want to lock all cells in B3:J49, or
Range(Cells("B43,J49")
if you want to lock only B3 and J49,
You probably want to unlock them in the Else part, but that's up to you.
Do I have to save the worksheet somewhere to be able to run it in the code?
You should save the workbook as a macro-enabled workbook (.xlsm) and make sure that you allow macros when you open it.
- Yes I have that. I think my macro is still being formed in Thisworksheet rather than a specific sheet. Is this okay?
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.