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).
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).
- Thank you so much, that seems to be working great. Thanks for all your help
I'll send the modified workbook back by PM.
Here is my version of the code:
Private Sub Worksheet_Change(ByVal Target As Range) 'protect grayed cells depending customer selected If Intersect(Range("C2"), Target) Is Nothing Then Exit Sub End If Me.Unprotect Range("B4:J86").Locked = False Select Case Range("C2").Value Case "X" Range("G4:H10").Locked = True Range("I12:J14").Locked = True Range("G15:J19").Locked = True Range("E16:F16").Locked = True Range("C21:D21").Locked = True Range("E20:F23").Locked = True Range("C28:J42").Locked = True Range("G50:J54").Locked = True Range("C55:J86").Locked = True Case "Y" Range("C22:D23").Locked = True Range("E22:F22").Locked = True Range("E28:F31").Locked = True Range("C43:J49").Locked = True Range("C55:J60").Locked = True Case "Z" Range("G50:J54").Locked = True Range("C55:J86").Locked = True Range("C28:J42").Locked = True Range("C21:D21").Locked = True Range("G4:H10").Locked = True Range("E20:F23").Locked = True Range("G15:J19").Locked = True Range("I12:J14").Locked = True Case "W" Range("C22:D23").Locked = True Range("B43:J49").Locked = True Range("B74:J86").Locked = True End Select Me.Protect End Sub- I just private messaged you with the workbook
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.
- Okay, so all was working until it wasn't. I haven't changed my code but nothing seems to be locking anymore. I read online about locking the entire sheet and then specifying which cells you want to unlock, rather than which you want to lock. Would you recommend this? How would you go about coding that? Do you have any other recommendations as to why the code was initially working and now isn't?
Make sure that you protect the sheet again in each of the situations.
- I figured it out. Thanks for all your help.
HansVogelaar Would something like this be possible?
- Okay that makes sense. Is there a way to incorporate if else statements in so that if one reports true the sequence stops?
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"?
- Since the value is "X", the range E20:F23 is locked.
- Since the value is not "Z", the range E22:F22 is unlocked. E10:F21 and E23:F23 remain locked.
- 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.
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?
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- 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?
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.
- 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?
- Thank you very much, that worked!!