need help with Worksheet_Change code
 

Can someone help me write the following code to say the following?

If someone is trying to change the value of cell E5 and the current content
of cell E5 is 0 then the contents cannot be change. But, if they are trying
to change the value of cell E5 and the current content of cell E5 is 0 then
they can change it to 0. Thanks very much.

need help with Worksheet_Change code
 

Option Explicit

Dim prevVal As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "E5"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Not IsEmpty(prevVal) Then
If prevVal = 0 Then .Value = prevVal
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
prevVal = Target
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Alex" wrote in message
...
Can someone help me write the following code to say the following?

If someone is trying to change the value of cell E5 and the current
content
of cell E5 is 0 then the contents cannot be change. But, if they are
trying
to change the value of cell E5 and the current content of cell E5 is 0
then
they can change it to 0. Thanks very much.

need help with Worksheet_Change code
 

Maybe something along these lines:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Address = "$E$5" Then
Application.EnableEvents = False
v = Target.Value
Application.Undo
If Target.Value = 0 Then Exit Sub
If Target.Value 5 Then
Target.Value = v
Else
Exit Sub
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy



"Alex" wrote:

Can someone help me write the following code to say the following?

If someone is trying to change the value of cell E5 and the current content
of cell E5 is 0 then the contents cannot be change. But, if they are trying
to change the value of cell E5 and the current content of cell E5 is 0 then
they can change it to 0. Thanks very much.

need help with Worksheet_Change code
 

whoops,

Hit send too soon:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Address = "$E$5" Then
Application.EnableEvents = False
v = Target.Value
Application.Undo
If Target.Value = 0 Then
Application.EnableEvents = True
Exit Sub
If Target.Value 5 Then
Target.Value = v
Else
Application.EnableEvents = True
Exit Sub
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub


It could be more compact, but you should get the idea.
--
Regards,
Tom Ogilvy


"Alex" wrote:

Can someone help me write the following code to say the following?

If someone is trying to change the value of cell E5 and the current content
of cell E5 is 0 then the contents cannot be change. But, if they are trying
to change the value of cell E5 and the current content of cell E5 is 0 then
they can change it to 0. Thanks very much.

need help with Worksheet_Change code
 

I already have some other code in Worksheet_Change. Do I just add your code
under my current code, which is:

If Target.Address = "$B$5" Then
Application.EnableEvents = False
Range("C16").Value = Null
Range("E16").Value = Null
Range("C5").Value = Null
Application.EnableEvents = True
End If

Thanks -

"Tom Ogilvy" wrote:

whoops,

Hit send too soon:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Address = "$E$5" Then
Application.EnableEvents = False
v = Target.Value
Application.Undo
If Target.Value = 0 Then
Application.EnableEvents = True
Exit Sub
If Target.Value 5 Then
Target.Value = v
Else
Application.EnableEvents = True
Exit Sub
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub


It could be more compact, but you should get the idea.
--
Regards,
Tom Ogilvy


"Alex" wrote:

Can someone help me write the following code to say the following?

If someone is trying to change the value of cell E5 and the current content
of cell E5 is 0 then the contents cannot be change. But, if they are trying
to change the value of cell E5 and the current content of cell E5 is 0 then
they can change it to 0. Thanks very much.

need help with Worksheet_Change code
 

I had yet another omission. Here is is with yoru code included.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Address = "$B$5" Then
Application.EnableEvents = False
Range("C16").ClearContents
Range("E16").ClearContents
Range("C5").ClearContents
End If


If Target.Address = "$E$5" Then
Application.EnableEvents = False
v = Target.Value
Application.Undo
If Target.Value = 0 Then
Application.EnableEvents = True
Exit Sub
ElseIf Target.Value 5 Then
Target.Value = v
Else
Application.EnableEvents = True
Exit Sub
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"Alex" wrote:

I already have some other code in Worksheet_Change. Do I just add your code
under my current code, which is:

If Target.Address = "$B$5" Then
Application.EnableEvents = False
Range("C16").Value = Null
Range("E16").Value = Null
Range("C5").Value = Null
Application.EnableEvents = True
End If

Thanks -

"Tom Ogilvy" wrote:

whoops,

Hit send too soon:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Address = "$E$5" Then
Application.EnableEvents = False
v = Target.Value
Application.Undo
If Target.Value = 0 Then
Application.EnableEvents = True
Exit Sub
If Target.Value 5 Then
Target.Value = v
Else
Application.EnableEvents = True
Exit Sub
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub


It could be more compact, but you should get the idea.
--
Regards,
Tom Ogilvy


"Alex" wrote:

Can someone help me write the following code to say the following?

If someone is trying to change the value of cell E5 and the current content
of cell E5 is 0 then the contents cannot be change. But, if they are trying
to change the value of cell E5 and the current content of cell E5 is 0 then
they can change it to 0. Thanks very much.

need help with Worksheet_Change code
 

This is kind of working. If there is already a 0 in the cell then it must
remain 0, regardless of what is typed in the cell. I'll put a msgbox that
says,"must remain 0" However, if there is a number 0 already in the cell,
then it's letting the user type a 0 in the cell. The way it should work is
if the current number in the cell is 0 then I need a msgbox to appear that
says "must be greater than 0" and the cell contents cannot change unless they
actually type in a number 0. Thanks.

"Tom Ogilvy" wrote:

I had yet another omission. Here is is with yoru code included.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Address = "$B$5" Then
Application.EnableEvents = False
Range("C16").ClearContents
Range("E16").ClearContents
Range("C5").ClearContents
End If


If Target.Address = "$E$5" Then
Application.EnableEvents = False
v = Target.Value
Application.Undo
If Target.Value = 0 Then
Application.EnableEvents = True
Exit Sub
ElseIf Target.Value 5 Then
Target.Value = v
Else
Application.EnableEvents = True
Exit Sub
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"Alex" wrote:

I already have some other code in Worksheet_Change. Do I just add your code
under my current code, which is:

If Target.Address = "$B$5" Then
Application.EnableEvents = False
Range("C16").Value = Null
Range("E16").Value = Null
Range("C5").Value = Null
Application.EnableEvents = True
End If

Thanks -

"Tom Ogilvy" wrote:

whoops,

Hit send too soon:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Address = "$E$5" Then
Application.EnableEvents = False
v = Target.Value
Application.Undo
If Target.Value = 0 Then
Application.EnableEvents = True
Exit Sub
If Target.Value 5 Then
Target.Value = v
Else
Application.EnableEvents = True
Exit Sub
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub


It could be more compact, but you should get the idea.
--
Regards,
Tom Ogilvy


"Alex" wrote:

Can someone help me write the following code to say the following?

If someone is trying to change the value of cell E5 and the current content
of cell E5 is 0 then the contents cannot be change. But, if they are trying
to change the value of cell E5 and the current content of cell E5 is 0 then
they can change it to 0. Thanks very much.