Using protected cells

[maddyc]

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.

[#2]

@maddyc 

You could use VBA code for that - would that be acceptable?

[#3]

@Hans Vogelaar Yes that would be my strategy to too use VBA to complete the question

[#4]

Yes, I was doing some more research and it seems like that is the only way

[#5]

@maddyc 

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

[#6]

@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 If

End Sub

[#7]

@maddyc 

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.

[#8]

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?

[#9]

@maddyc 

You should save the workbook as a macro-enabled workbook (.xlsm) and make sure that you allow macros when you open it.

[#10]

Yes I have that. I think my macro is still being formed in Thisworksheet rather than a specific sheet. Is this okay?

[#11]

@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.

[#12]

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.

[#13 · Accepted solution]

@maddyc 

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 Sub

See the small sample workbook attached to this reply (you'll have to allow macros).

[#14]

Thank you very much, that worked!!

[#15]

This seems to be working, but it also seems to be locking all of the other cells surrounding the specified range. Is there a way to keep the rest of them unlocked?

[#16]

@maddyc 

By default, ALL cells are locked when you protect the sheet.

Click Unprotect Sheet on the Review tab of the ribbon.

Select the entire sheet, or at least the cells that the user should be able to edit.

Press Ctrl+1 to activate the Format Cells dialog.

Activate the Protection tab.

Clear the Locked check box.

Click OK.

Click Protect Sheet on the Review tab of the ribbon, then click OK.

[#17]

Okay so one last question: I am trying to do this with multiple inputs to "C2". For example: instead of just "X", sometimes I want to change it to "Y" or "Z" as well. I have been using this code but it doesn't seem to capture all the the different inputs. Would you recommend using and if else?

[#18]

@maddyc 

Here is an example:

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")
        Range("B50:J56").Locked = (UCase(Range("C2").Value) = "Y")
        Range("B57:J63").Locked = (UCase(Range("C2").Value) = "Z")
        Me.Protect
    End If
End Sub

[#19]

@Hans Vogelaar 

I have tried the method you shared below. I was working on something like that before you said that, the problem is it still only randomly choses one or two values to lock and the other ones are ignored. For example I will input this code and the cells will only be locked for "Z". Any thoughts?

[#20]

@maddyc 

You specify the same or overlapping ranges for some values. For example, your code contains lines

        Range("E20:F23").Locked = (UCase(Range("C2").Value = "X")
        Range("E22:F22").Locked = (UCase(Range("C2").Value = "Y")
        Range("E20:F23").Locked = (UCase(Range("C2").Value = "Z")

What happens if you enter "X"?

1.  Since the value is "X", the range E20:F23 is locked.
2. Since the value is not "Z", the range E22:F22 is unlocked. E10:F21 and E23:F23 remain locked.
3. Since the value is not "Y", the entire range E20:F23 is unlocked. The effect of lines 1 and 2 is undone.

So you will carefully have to think through your logic to avoid such interference.