A vba annoyance

  Josquius 14:08 13 Mar 2005
Locked

Is there anyway to fix it on VBA so that when you press cancel on pop up boxes it doesn't come up with a debug error but actually cancels?

  VoG II 14:38 13 Mar 2005

More information needed.

  Josquius 14:46 13 Mar 2005

In excel using VBA a inputbox asks for something, you normally type this in and press OK.
There is also a cancel button which should stop running the code however pressing the cancel causes a type mismatch all the type and brings up a excel error.

  VoG II 14:59 13 Mar 2005

If you were to use the following statement


iNumber = InputBox(prompt:="Please enter a number")


you will get a type mismatch error message since variable iNumber has been declared to be an integer and the input box always returns text.

Using Val in front of the InputBox will convert the response into a number and avoids the error message:


iNumber = Val(InputBox(prompt:="Please enter a number"))


However, if the user selects cancel, the resulting "" would be converted to a 0. And there would be no way to determine if the user selected cancel or
entered a 0.

If zero is not a valid response and you do not wish to confirm that the user pressed the cancel button, then you can use the following instead:


iNumber = Val(InputBox(prompt:="Please enter a number"))

If iNumber = 0 Then End

  VoG II 15:02 13 Mar 2005

Sub Get_A_Number()

Dim response As Variant, numberEntered As Integer

response = Application.InputBox (prompt:="Please enter a number", Type:=1)

If Str(response) = "False" Then Exit Sub

numberEntered = response

MsgBox "You entered " & numberEntered

End Sub

  Josquius 14:03 18 Mar 2005

How would you do it for a string inputbox.
If response="false" doesn't seem to pick up on pressing a cancel button there.

  VoG II 15:58 18 Mar 2005

If Str(response) = "False"

should work.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?