Lock a cell based on another cell's value

  • Thread starter Thread starter ryanb.
  • Start date Start date
R

ryanb.

For Example:

I want cell M4 to be locked if Cell E4 is equal to 0. If this is possible,
would the user be required to unprotect then re-protect the sheet to make
the lock effective? Just curious if there is a way to make it work.

Thanks,

ryanb.
 
One way, using an event macro:

put this in your worksheet code module (right-click on the sheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const PWORD As String = "drowssap"
If Target.Address(False, False) = "E4" Then
Me.Unprotect Password:=PWORD
Range("M4").Locked = Target.Value = 0
Me.Protect Password:=PWORD
End If
End Sub

This assumes that E4 is a manual entry. If it's a calculated value,
use the Worksheet_Calculate event instead:

Private Sub Worksheet_Calculate()
Const PWORD As String = "drowssap"
Me.Unprotect Password:=PWORD
Range("M4").Locked = Range("E4").Value <> 0
Me.Protect Password:=PWORD
End Sub
 
Back
Top