Forum Discussion

Copper Contributor

Jul 28, 2022

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

excel

HansVogelaar
Aug 01, 2022
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).
Demo.xlsm17 KB

maddyc

Copper Contributor

Aug 03, 2022

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?

HansVogelaar

MVP

Aug 03, 2022

maddyc

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.

maddyc
Copper Contributor
Aug 04, 2022
Thank you so much, that seems to be working great. Thanks for all your help

HansVogelaar

MVP

Aug 04, 2022

maddyc

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

maddyc
Copper Contributor
Aug 04, 2022
I just private messaged you with the workbook