locking cells dynamically
Try the following macro. For simplicity, it assumes that the normal state of
the worksheet is Protected, but that most of the cells are Unlocked:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B12")) Is Nothing Then
v = Range("B12").Value
If v = "no" Then
ActiveSheet.Unprotect
Range("C12").Locked = False
Range("C12").FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End If
If v = "yes" Then
ActiveSheet.Unprotect
Range("C12").Locked = True
Range("C12").FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End If
End If
End Sub
--
Gary''s Student - gsnu200738
"Deano" wrote:
Here is what I am trying to do:
I have protection set up so that the user can only enter unlocked cells. If
a certain cell (Say B12) contains "no", I want another cell (say C12) to be
unlocked, so the user can cursor to it. If that same cell (B12) contains
"yes", I want the cell (C12) to be locked so the user cannot cursor to it.
Is this possible?
Thanks,
Dean
|