How can I protect only cells that meet certain conditions?

[Guest]

I want to allow users to enter a value in a cell (say G5) only if another
cell (G21) has a value greater than zero.

If G21 is zero or blank, then I do not want the user to be able to select G5.

This is for Microsoft Xcel 2000.

 

[STEVE BELL]

You might use a worksheet event. This will undo any entry in any cell if
the cell 16 rows down is not greater than 0
Add the select to move active cell to the next cell down.

===========================================
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Offset(16, 0) <= 0 Then
Application.Undo
' Target.Offset(1, 0).Select
End If

Application.EnableEvents = True

End Sub
===========================================

For a specific cell:
===========================================
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If target.address = "$G$5" and Range("G21") <= 0 Then
Application.Undo
' Range("G6").Select
End If

Application.EnableEvents = True

End Sub
===========================================

 

[STEVE BELL]

This one will move the selection:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False

If Target.Address = "$G$5" And Range("G21") <= 0 Then
Target.Offset(1, 0).Select
End If

Application.EnableEvents = True
End Sub

 

[Guest]

I want to allow users to enter a value in a cell (say G5) only if another
cell (G21) has a value greater than zero.

If G21 is zero or blank, then I do not want the user to be able to select G5.

This is for Microsoft Xcel 2000.

Something along these lines should do the trick:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("G21") > 0 Then
ActiveSheet.Unprotect
Range("G5").Locked = False
ActiveSheet.Protect
End If
End Sub

 

[William Benson]

Yes but remember to have the alternative ... to set the cell's protection to
locked if G21 is not in the permitted condition.