VBA to lock cells based on another cells value

[DHAM1963]

I have a sheet where values are entered in column B and at the bottom is a Counta formula to count non blanks. I have put in a VBA code on the sheet to lock cells based on the total in that Counta field but it is not working. Is that because cell B38 has a formula in it?. Thanks in advance for any help

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B38") < 5 Then
Range("B3:B37").Locked = False
ElseIf Range("B38") > 5 Then
Range("B3:B37").Locked = True
End If
End Sub

 

[Candyman8019]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B38").value < 5 Then
Range("B3:B37").Locked = False
ElseIf Range("B38").value > 5 Then
Range("B3:B37").Locked = True
End If
End Sub

 

[mumps]

The Worksheet_Change event will not be triggered by a change in the result of a formula. You will need to use a Worksheet_Calculate event.

Private Sub Worksheet_Calculate()
    If Range("B38") < 5 Then
        Range("B3:B37").Locked = False
    Else
        Range("B3:B37").Locked = True
    End If
End Sub

 

[DHAM1963]

The Worksheet_Change event will not be triggered by a change in the result of a formula. You will need to use a Worksheet_Calculate event.

Private Sub Worksheet_Calculate()
    If Range("B38") < 5 Then
        Range("B3:B37").Locked = False
    Else
        Range("B3:B37").Locked = True
    End If
End Sub

Hi, this did not work but I had wondered about a triggering event so the calculate makes sense rather than change. Does it matter from the start if sheet is protected or not, or is cells are locked or unlocked. The code is so basic I just cant see why its not working. I saved the sheet as an xlsm and I checked that

 

[Candyman8019]

You'll likely need to unprotect the sheet first. Note: change the sheet number to whichever you're working on.

Private Sub Worksheet_Calculate()
   Sheet1.Unprotect Password:="{your password here}"
    If Range("B38") < 5 Then
        Range("B3:B37").Locked = False
    Else
        Range("B3:B37").Locked = True
    End If
Sheet1.protect Password:="{your password here}"
End Sub

 

[mumps]

You actually don't need the sheet name since the event macro is in the code module for the active sheet.

Private Sub Worksheet_Calculate()
   ActiveSheet.Unprotect "your password here"
    If Range("B38") < 5 Then
        Range("B3:B37").Locked = False
    Else
        Range("B3:B37").Locked = True
    End If
    ActiveSheet.Protect "your password here"
End Sub

 

[DHAM1963]

You actually don't need the sheet name since the event macro is in the code module for the active sheet.

Private Sub Worksheet_Calculate()
   ActiveSheet.Unprotect "your password here"
    If Range("B38") < 5 Then
        Range("B3:B37").Locked = False
    Else
        Range("B3:B37").Locked = True
    End If
    ActiveSheet.Protect "your password here"
End Sub

Hi, thank you, this has worked well, I now need to do the same thing for C38:AF38. Do you think a nested if statement is my best bet here?

 

[mumps]

I now need to do the same thing for C38:AF38

Does this range also depend on the value in B38?

 

[Joe4]

Last question duplicated here: Extend VBA to further columns

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.