Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting out of a if loop
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting out of a if loop
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 In article , rk0909 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting out of a if loop
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 "rk0909" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting out of a if loop
thanks a lot works perfect.
RK "JE McGimpsey" wrote: 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 In article , rk0909 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop time seems dependent on unrelated workbook - Why? | Excel Worksheet Functions | |||
Loop gone crazy | Excel Discussion (Misc queries) | |||
Do Loop | Excel Discussion (Misc queries) | |||
hysteresis loop | Charts and Charting in Excel | |||
loop trough e-mail address list to send task lists with outlook | Excel Discussion (Misc queries) |