InputBox Question 2

[jisoo22]

Does anyone know the proper statement for when a user hits the "cancel" button, it stops the macro? My statement is obviously wrong but I figure it's somewhere along the lines of this:


If InputBox = vbCancel Then
Halt
End If


Thanks!
Jisoo22

 

[jisoo22]

I think I might have figured it out but not quite sure, can anyone tell me if this is right?


If InputBox("Please enter the dealer's number.", "Stop&quot = "" Then
Halt
End If

 

[Kaah]

yeah, you've got the idea!

kaah.

 

[daimaou]

jisoo22

In fact that's not the exact way of doing it, since if the user presses ok without writing anything your macro will also stop. The way of doing it is:

Dim strInput As String

strInput = InputBox(Please enter the dealer's number.", "Stop&quot

If StrPtr(strInput) = 0 Then
Halt
End If

Hope this helps

 

[jisoo22]

Thanks! Now I have this thing working. I have a similar question now. I have the code working for input where if the contents of the cell are numeric then it works. Unfortunately I have a couple of cells where there must be letters too. I tried other functions like "IsEmpty" by itself or adding "IsNull" but it doesn't work. The problem is that the macro just goes right by without working or even giving an error message. Can anyone give me an idea? Here is the code I have right now that works for numeric content:


dim d
d = Range("B9&quot.Value

If IsEmpty(d) Or Not IsNumeric(d) Then
MyAnswer = ""
Do Until IsNumeric(MyAnswer)
MyAnswer = InputBox("Please enter the model number.", "You must complete the form!&quot
If MyAnswer = "" Then Exit Sub
Loop
Range("B9&quot.Value = MyAnswer
ElseIf Asc(d) = 32 Then
MyAnswer = ""
Do Until IsNumeric(MyAnswer)
MyAnswer = InputBox("Please enter the model number.", "You must complete the form!&quot
If MyAnswer = "" Then Exit Sub
Loop
Range("B9&quot.Value = MyAnswer
End If


Thanks!

 

[Dustman]

I might be misunderstanding what you are wanting to do, but why can't you just check to see if it is blank? You can use the trim() function to take care of spaces. For example

If trim(d) = "" Then ...

If all you are wanting to do is ensure it is not blank, that should work fine. If you actually need to look up the numbers and letters individualy, You have to use the left() and right() functions. For example if they put "XX1234" you could check with:

if IsNumeric(left(d,2)) = false and IsNumeric(right(d,4)) then...

I don't know if that even applies to what you need but it might come in handy sometime. -Dustin
Rom 8:28

 

[Kaah]

Jisoo,

the problem with your code above is that you're using Pre-Test repetition rather than Post-Test (look these up as they are very important programming concepts and are core to any language you will ever code in).

change your loop statements so that the condition is at the end, not the beginning like so

do
iMyAnswer = inputbox("Please Enter Model number&quot
if imyanswer="" then exit sub
loop until isnumeric(imyanswer)

this will fix the problem you describe above.

hope this helps...

kaah