Jul 28 2022 12:13 PM
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.
Jul 28 2022 12:37 PM
You could use VBA code for that - would that be acceptable?
Jul 28 2022 12:47 PM
@Hans Vogelaar Yes that would be my strategy to too use VBA to complete the question
Jul 28 2022 12:51 PM
Jul 28 2022 12:56 PM
You can create a Worksheet_Change event procedure in the worksheet module that checks whether A2 has been changed. If so:
Jul 29 2022 08:08 AM
@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
Jul 29 2022 08:15 AM
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.
Jul 29 2022 08:21 AM
Jul 29 2022 08:25 AM
You should save the workbook as a macro-enabled workbook (.xlsm) and make sure that you allow macros when you open it.
Jul 29 2022 10:51 AM
Jul 29 2022 12:49 PM
@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.
Aug 01 2022 12:31 PM
Aug 01 2022 12:54 PM
SolutionIn 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).
Aug 02 2022 09:16 AM
Aug 02 2022 10:16 AM
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.
Aug 02 2022 12:07 PM
Aug 02 2022 01:43 PM
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
Aug 03 2022 04:53 AM
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?
Aug 03 2022 05:43 AM - edited Aug 03 2022 05:44 AM
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"?
So you will carefully have to think through your logic to avoid such interference.
Aug 01 2022 12:54 PM
SolutionIn 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).