getting out of a if loop

[Guest]

hi All,
I have a VBA code for a change event associated with a particular cell, say
A1. If the value in A1 is less than 500 a message box is displayed that you
have to enter something freater than 500. If it is less than 500 I want the
value of A1=0. But now since 0 is less than 500 code goes into an infinite
loop of showing the msgbox. How do I get out of this loop.
Any help or guidance is appreciated.
Thanks,
RK

 

[JE McGimpsey]

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Address(False, False) = "A1" Then
If IsNumeric(.Value) Then
If .Value < 500 Then
MsgBox "Must be 500 or greater"
Application.EnableEvents = False
.Value = 0
Application.EnableEvents = True
End If
End If
End If
End With
End Sub

 

[Guest]

I would use Data/Validation, set the minimum value at 500 and use the
facilities within this to provide the necessary message.

If you set the original value to 0, before setting the validation, the user
can leave the value at 0 by using Cancel.

Roger

 

[Guest]

thanks a lot works perfect.
RK

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Address(False, False) = "A1" Then
If IsNumeric(.Value) Then
If .Value < 500 Then
MsgBox "Must be 500 or greater"
Application.EnableEvents = False
.Value = 0
Application.EnableEvents = True
End If
End If
End If
End With
End Sub