locking cells dynamically

[Guest]

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

 

[Guest]

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