locking cells dynamically
 

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

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