endless loop - using input box

[audreyanderson1]

In Excel VBA...

Ok I'm writing a program that asks the user to input a number. If it
is not a valid number, they get an error and they can either "retry"
or "cancel"

now I just need to figure out how to put this in a loop, so they can
keep clicking "retry" if necessary or exit the sub if they click
cancel. however the loop I tried didn't work...it goes into endless
loop. Any suggestions? Thanks


If Sheets("Sales").Range("B1").Text = "#N/A" Then
'Checks for invalid number'
Output = MsgBox("You have entered an invalid Part
Number", _
vbRetryCancel, "Invalid Number")

Do
temp = Val(Output)
If temp = vbCancel Then ' User chose Cancel.
Exit Sub 'exit and clear.

ElseIf temp = vbRetry Then 'user chose retry
partNum = InputBox("Please enter the part number",
"Part Number", 0)

End If

Loop While Sheets("Sales").Range("B1").Text = "#N/A"

 

[Greg Glynn]

Try:

partNum = InputBox("Please enter the part number", "Part Number",
0)

Sheets("Sales").Range("B1").Value = PartNum

While Sheets("Sales").Range("B1").Text = "#N/A"

Output = MsgBox("You have entered an invalid Part Number", _
vbRetryCancel, "Invalid Number")

temp = Val(Output)
If temp = vbCancel Then ' User chose Cancel.
Exit Sub 'exit and clear.

Loop

 

[Greg Glynn]

Cleaner Still:

partNum = InputBox("Please enter the part number", "Part Number",
0)

Sheets("Sales").Range("B1").Value = PartNum

While Sheets("Sales").Range("B1").Text = "#N/A"

Output = MsgBox("You have entered an invalid Part Number",
vbRetryCancel, "Invalid Number")

If Val(Output) = vbCancel Then Exit Sub

Loop