Trouble with InputBoxes
Here is a way to catch most outcomes of interacting with the dialog;
Dim value As String
value = InputBox("Please enter a #", "Determine Limit", 10000)
If (StrPtr(value) = 0) Then
MsgBox "You pressed cancel or [X]"
ElseIf (value = "") Then
MsgBox "You did not enter anything"
ElseIf (Val(value) = 0 And value <> "0") Then
MsgBox "Invalid number"
Else
MsgBox "You entered " & value
End If
When in doubt, check the inbuilt VBA help ;)
InputBox()
returns a String
You can try this for Integers
Sub Sample()
Dim Ret As String, userInputValue As Integer
'Text to display, Title, Default Value
Ret = InputBox("Please enter a #", "Determine Limit", 10000)
If Ret = "" Then
MsgBox ("You pressed the cancel button... or you pressed OK without entering anything")
Else
If IsNumeric(Ret) Then
userInputValue = Val(Ret)
Else
MsgBox ("Incorrect Value")
End If
End If
End Sub
InputBox
returns a string regardless of the number the user enters. If they click Cancel, it returns an empty string.
Try this in the Immediate window.
? TypeName(InputBox("Please enter a #", "Determine Limit", 10000))
String
For the test in your code, check whether the numerical equivalent of userInputValue
is equal to zero.
If Val(userInputValue) = 0 Then
MsgBox ("You pressed the cancel button...")
End If
Note than InputBox
doesn't allow you to distinguish whether the user clicked Cancel or deleted the starting value (10000) and clicked OK. Either way, InputBox
returns an empty string (""). And Val("")
also returns zero. If that will be a problem, substitute a custom form to gather the user input ... which is not as limited as InputBox
.