Trouble with InputBoxes

Question

I'm currently working with InputBoxes in MS Access VBA. I'm examining validation and handling how the user interacts with the InputBox through pressing the OK or Cancel buttons.

Correct me if I'm wrong but InputBoxes can return any data type and by default return a string? For example:

Dim userInputValue As String

'Text to display, Title, Default Value
userInputValue = InputBox("Please enter a #", "Determine Limit", 10000)

If userInputValue = "" Then
    MsgBox ("You pressed the cancel button...")
End If

If the user presses the Cancel button this will run fine.

But when I swap this for an integer value like so:

Dim userInputValue As Integer
'Text to display, Title, Default Value
userInputValue = InputBox("Please enter a #", "Determine Limit", 10000)

If userInputValue = 0 Then
    MsgBox ("You pressed the cancel button...")
End If

I receive a Type Mismatch: Runtime Error '13' Why is this? When I debug the code and look at what is being returned I find that the userInputValue is actually 0, which is what I'm checking for. So is the problem that the InputBox is actually returning a string?

Upu

Answer 1

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

Chalcidice

Answer 2

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

Rendering

@HansUp: StrPtr (like VarPtr, VarPtrArray, VarPtrStringArray, and ObjPtr) is an undocumented function that is used to get the underlying memory address of variables vb.mvps.org/tips/varptr.asp – Chalcidice

Sure, its part of the core language & works for detecting a string thats never been assigned to – Rendering

+ 1 Another nice way :) offtopic: Wonder what "Yes" implies in your profile :P – Chalcidice

Thanks, guys. I didn't know that was available in VBA. – Hedrick

@SiddharthRout I think I just added it to get the "fill your profile" bronze badge :) – Rendering

lol@Alex: For future visitors: VarPtr, StrPtr and ObjPtr are still present in VBA in Off2007 and Off2010. For 64-bit pointers in the 64-bit version of Office 2010, you may find a documentaiton here in msdn: msdn.microsoft.com/en-us/library/ee691831.aspx (Sorry Alex for hijacking this comment) – Chalcidice

Heads up: This is the correct way to detect Cancel / close over empty string. – Mackintosh

Answer 3

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.

Hedrick

Answer 4

Note: The following applies only to Excel. The Application.InputBox function is not available in Access:

Application.InputBox returns "False" if the user clicks Cancel.

Dim userInputString As String
Dim userInputValue As Integer
'Text to display, Title, Default Value
userInputString = Application.InputBox("Please enter a #", "Determine Limit", 10000)
If userInputString = "False" Then
    MsgBox ("You pressed the cancel button...")
Else
    userInputValue = CInt(Trim(userInputString))
End If

Limbic

"False" is not (never) returned, only if you type it. Pressing Cancel returns an empty string. – Anders

@Anders I'm sorry, but you're wrong. At least try something before you downvote it. You'll find out that I'm right. This is Application.Inputbox and it works that way if the variable is declared as a String. It works differently if the variable is declared as Variant. – Limbic

There is no Application.InputBox in Access so I assumed you meant VBA.InputBox ... – Anders

OK, my bad, it's in Excel, but it's not in Access for some reason. – Limbic

@Limbic this solved by issue in Excel. Works perfectly. – Ovary

@moderator This same question for Excel is marked as a duplicate and redirected to this question, but the answers are different for Access and Excel. – Limbic

Answer 5

Rather than getting back a number back from InputString, set the default string to "___________". Then I can test if anything was entered and the Cancel" key will return a null string.

I also add a test value "Allow_Empty_String" (True/False) because sometimes I want an empty string back.

The "Flag_Msg_Err" has to be reset to False because "Msg_Err" sets it to true to catch calls from loops.

The routine needs to respond in one of 3 ways: 1. If the "Cancel" button is pressed, the program ends. 2. If the "OK" button is pressed, IF ALLOW_EMPTY_STRING THEN a message is given and input starts again ELSE an empty string is returned END IF 3. That string was entered., then TRIM(STRING) is returned.

Code:

`Function Input_String(Prog, Message, Optional Allow_Empty_String = False) ' Returns a String or the word "Cancal'". ' 2/28/19 Created. WML

If Trace Then Prog = Prog & "(*)"
Call Name_Put("Flag_Msg_Err", False)

Do
    Test_String = "_____________"
    Input_String = InputBox(Prompt:=Message, _
                            Default:=Test_String, Title:=Prog)

    Select Case Input_String

        Case "TRACE"
            ' for Debugging
            Old_Trace = Named("Flag_Trace")
            New_Trace = Not Old_Trace
            Call Name_Put("Flag_Trace", New_Trace)
            Msg = "TRACE is now set to " & New_Trace & "."
            Call Name_Put("Flag_Msg_Err", False)

        Case Test_String
            If Allow_Empty_String Then
                Msg = "You must enter something,|" & _
                      " or select CANCEL."
                Call Msg_Err(Prog, Msg, , True)
                Call Name_Put("Flag_Msg_Err", False)
            Else
                Input_String = ""
                Finished = True
            End If

        Case ""
            If Trace Then
               Stop: Exit Function
            Else
               End
            End

        Case Else
            ' If entered a space or clicked "Ok".
            Input_String = Trim(Input_String)
            Finished = True

    End Select

 Loop

End Function ' Input_String`

Olette