Avoiding error on Input Box - if no value is entered

[dhstein]

I have a line like this:

MyData = InputBox(Prompt:="Enter Data", Title:="ENTER Data")

If the user selects "Cancel", I get a Run-time error 13 - Type Mismatch.
How can I avoid this error when the user selects cancel ? Thanks for any
help on this.

 

[Gary''s Student]

InputBox will return 0 if Cancel is pressed.

Make sure you have Dim'ed MyData so as to accept 0

 

[Jacob Skaria]

Check the result and Exit Sub

MyData = InputBox(Prompt:="Enter Data", Title:="ENTER Data")
If Trim(MyData) = "" Then Exit Sub

OR

If you want to force the user to enter something then keep this within a loop

Do
MyData = InputBox(Prompt:="Enter Data", Title:="ENTER Data")
Loop Until Trim(MyData) <> ""


If this post helps click Yes

 

[Gary''s Student]

Sorry about the first post, ignore it. Try:

Sub qwerty()
x = InputBox(prompt:="feed me")
MsgBox (x)
x = Application.InputBox(prompt:="feed me")
MsgBox (x)
End Sub

The first call will return a null string.
The second call will retrun FALSE.

So MyData must be Dim'ed to handle the null string.

 

[Jacob Skaria]

Oops..If that is a type mismatch check your declaration and adjust the code
as below to handle blanks

Dim mydata As Integer
mydata = CInt("0" & InputBox(Prompt:="Enter Data", Title:="ENTER Data"))


If this post helps click Yes

 

[dhstein]

Thanks for all the responses. I used Gary's method, Dim'ed the variable as
Variant and it's working.

 

[Abu Haniffa]

Hi, this is my first post and I have a problem. I am still a baby when it comes to VBA. I have this problem. In my input box when I click cancel it shows an error message "Type Mismatch error 13" What does it mean? I have attached a macro "Duplicate" to the above. No problem. When the user inputs a number x into the input box, it runs the duplicate macro x times. If the user clicks OK there is also no problem since the default value is 0. Its only when the user clicks cancel. Any help please?

The function looks like this.

Private Sub Command20_Click()
Dim intHowMany As Integer
Dim intCounter As Integer


intHowMany = InputBox(Prompt, "How Many", 0)
For intCounter = 1 To intHowMany
DoCmd.RunMacro "Duplicate"
Next intCounter
End Sub