VBA to lock cells based on another cells value

DHAM1963

New Member
Joined
Jan 25, 2022
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Candyman8019

Active Member
Joined
Dec 2, 2020
Messages
498
Office Version
  1. 365
Platform
  1. Windows
Try this:
VBA Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
12,165
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
VBA Code:
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

New Member
Joined
Jan 25, 2022
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

Active Member
Joined
Dec 2, 2020
Messages
498
Office Version
  1. 365
Platform
  1. Windows
You'll likely need to unprotect the sheet first. Note: change the sheet number to whichever you're working on.

VBA Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
12,165
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
You actually don't need the sheet name since the event macro is in the code module for the active sheet.
VBA Code:
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

New Member
Joined
Jan 25, 2022
Messages
14
Office Version
  1. 365
Platform
  1. Windows
You actually don't need the sheet name since the event macro is in the code module for the active sheet.
VBA Code:
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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,515
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,176,264
Messages
5,902,229
Members
434,952
Latest member
AlanN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top