Exiting a loop...

[mjack003]

Hi,

I have a macro which prompts the user for a number and moves on. Th
problem is it continuously loops until you hit "OK". The cancel butto
does not work and instead just reloads the prompt window. How would
go about exiting the loop in the following code?

Do
mystr = InputBox(prompt:="Enter Shipping Document Number:")
If Trim(mystr) = "" Then

End If

If IsNumeric(mystr) Then
If Val(mystr) = CLng(mystr) _
And Val(mystr) < 999999 Then
mystr = Format(Val(mystr), "000000")
Exit Do
End If
End If
Loop

Any suggestions would be great.

Best Regards,
Mjac

 

[Frank Kabel]

Hi
try
Do
mystr = InputBox(prompt:="Enter Shipping Document Number:")
If Trim(mystr) = "" Then
Exit Do
End If

 

[Rollin_Again]

Can you explain in more detail exactly what you are trying to accomplis
with this macro? Your DO statement does not have any condition tha
needs to be met for the code to be executed. Hitting the Cancel butto
does not return a result that can be used to check which button wa
pushed. Pressing CANCEL basicall gives a result which is th
equivalent of leaving the inputbox blank and hitting OK. Try addin
either the word END or EXIT DO to your first IF statement. That wil
basically say that if you leave the inputbox blank, you will eithe
EXIT the sub completely or Exit the Loop. Your other option is t
create a custom form and set one of the custom buttons to end th
execution of the code




Do
mystr = InputBox(prompt:="Enter Shipping Document Number:")
If Trim(mystr) = "" Then

END ' USE THIS TO END THE SUB
EXIT DO ' USE THIS TO EXIT THE LOOP

End If[/]



Rolli

 

[mjack003]

That was simple enough Thank you for the help. I have one mor
question though. In my workbook I have a macro which copies a templat
sheet which has formulas in some of the cells. How would I go abou
copying the values instead of the formulas using VBA?

Regards,
Mjac

 

[Rollin_Again]

Make your copy selection and then use

Selection.PasteSpecial Paste:=xlValues



Rolli