Forum Discussion

maddyc's avatar
maddyc
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's avatar
    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).

maddyc's avatar
maddyc
Copper Contributor
Jul 29, 2022

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

HansVogelaar's avatar
HansVogelaar
MVP
Jul 29, 2022

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.

  • maddyc's avatar
    maddyc
    Copper Contributor
    Jul 29, 2022
    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?
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jul 29, 2022

      maddyc 

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

      • maddyc's avatar
        maddyc
        Copper Contributor
        Jul 29, 2022
        Yes I have that. I think my macro is still being formed in Thisworksheet rather than a specific sheet. Is this okay?

Resources