How can I protect only cells that meet certain conditions?
 

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.

How can I protect only cells that meet certain conditions?
 

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
===========================================
--
rand451
"Billparsons40" wrote in message
...
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.

How can I protect only cells that meet certain conditions?
 

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

--
rand451
"STEVE BELL" wrote in message
news:I3Mme.10044$3u3.1713@trnddc07...
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
===========================================
--
rand451
"Billparsons40" wrote in message
...
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.

How can I protect only cells that meet certain conditions?
 

"Billparsons40" wrote:

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

How can I protect only cells that meet certain conditions?
 

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

"bigwheel" wrote in message
...
"Billparsons40" wrote:

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