A vba annoyance

  Josquius 14:08 13 Mar 05
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 05

More information needed.

  Josquius 14:46 13 Mar 05

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 05

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 05

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 05

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 05

If Str(response) = "False"

should work.

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

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…