Forum Discussion
Solved
Using protected cells
I am trying to accomplish the following:
I have a document where depending one what is selected in A2 - cells are highlighted, based on conditional formatting, to either stay the same or be grayed out. If they are grayed out they do not need to be filled out based on what was selected in A2. Is there a way to protect only the cells that are grayed out. The problem I am having with this is that what needs to be protected changes every time something different is selected in A2. Is there a way to easily protect only grayed out cells - even if what cells are gray changes every time? Please let me know.
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).
maddyc Does anyone have any initial thoughts on starting the code. I was going to try something like this, but am having trouble running it in the file.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(ActiveSheet.Cells(C2), Target) Is Not Nothing Then
If ActiveSheet.Cells(2, 7).Text = "X" Then
ActiveSheet.Range(Cells(B43), Cells(J49)).Locked = True
Else
ActiveSheet.Range(Cells(3, 7), Cells(66, 7)).Locked = False
End If
End IfEnd Sub
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.
- Yes, the A2 value is now C2. I am pretty new to VBA, so I appreciate all the tips.
Do I have to save the worksheet somewhere to be able to run it in the code?
You could use VBA code for that - would that be acceptable?
- Yes, I was doing some more research and it seems like that is the only way
You can create a Worksheet_Change event procedure in the worksheet module that checks whether A2 has been changed. If so:
- Unprotect the sheet.
- Unlock cells that should be editable depending on the value of A2.
- Lock cells that should not be editable depending on the value of A2.
- Protect the sheet again
HansVogelaar Yes that would be my strategy to too use VBA to complete the question
