Excel VBA InputBox Explained

Prompt the user for required input, using an easy to interact with input box in your Excel VBA program. All you need to know about the InputBox function is right here. Oh, do not forget to validate!

What is an InputBox and When to Use It

In last week’s Blog post I covered in great detail the Excel VBA MsgBox function. I will not repeat how user interaction is incredibly important, but as you now know how to present informative messages to your user, sometimes you also need some input from the user to feed your program.

As you probably know, you can employ VBA User Forms for highly customizable interaction dialogs with the user, as I explain and demonstrate on this Blog post about User Forms.

However, the readily available and simple to use InputBox may be a quick solution for when we only need a single input from the user.

The InputBox is yet another service offered to us by the Windows operating system without the need for us to design and implement it, just call and use it.

The Excel VBA InputBox Function

The anatomy of a Windows input box is shown in the following image.

There are several aspects of the input box we have control over, when we call the InputBox function to ask the user for some input:

1. The Window title
2. The prompt (message) text
3. A default value to be offered within the input box
4. An x,y coordinates pair indicating the placement of the InputBox window on the screen

In addition, we can also associate a help file with the input box, allowing the user to open a help window with our explanations. However, the supported helpfile format (HLP) is very outdated and this feature is not to be relied on anyway, so I never really use it.

As the InputBox is serviced by Windows, some aspects of the input box are coming from your Windows settings, such as the font size and Window colors (background, title, frame). Some of these are changeable in your Windows Display and Appearance setting.

Calling the InputBox Function in Excel VBA

The InputBox function returns the value shown in the input box (textbox control) upon confirming with the OK button, or an empty string if cancelled. The returned value is always a string.

The function expects at least one argument: the prompt (message) text to show to the user. If that’s the only argument supplied, the message box title will be “Microsoft Excel”, there will be no default value placed in the input box and the window will be positioned at the center of the screen.

As you already know that user experience is important, I strongly advise to always provide a title to be placed on your messages.

The input box window always opens as a modal, meaning, it must be closed before focus can be shifted to other windows.

Here’s the function stub (arguments in square brackets are optional):

InputBox (prompt message, [ title, ] [ default, ] [ xPos, ] [ yPos, ] [ helpfile, context ])

Here’s an example:

Dim strMsgResponse as String

strMsgResponse = InputBox("How old are you?", "Your input is required")

You probably noticed that I haven’t specified the position of the input box, I’m fine with the default center screen.

The strMsgResponse variable will hold the returned value, as a string.

Let’s see another example:

Dim strMsgResponse as String

strMsgResponse = InputBox("Reason for participating:", "Your input is required", "Show my talent", 2000, 1000)

Here we are offering a default answer to make it easy for the user in most cases, as we predict that the most popular answer would be “Show my talent”. All there is left for the user to do is click “OK” (or press the Enter key on the keyboard, simulating a click on the OK button, which is the default button).

This time I also forced a position of the input box to be 2000 pixels to the right of the left edge of the screen, and 1000 pixels down from the top edge of the screen.

Input Validation

It is always important to validate the input we received from the user and never assume valid data was submitted!

As we have the user input returned in the receiving variable (strMsgResponse in out examples), we must validate it is a legal response from both a type and business logic perspectives, before we move on to use it in our program.

For example, asking for a user’s age, we expect to receive a numeric response, and perhaps we want to catch unnoticed typos by checking the age range. If the user inadvertently typed 256 instead of 26, we can identify that and ask for his input again.

The below Function shows but one way of forcing a valid, mandatory input, before carrying on with our program. Note that I am also checking to see is an empty string was provided (either by leaving the input box empty or by cancelling the dialog altogether) and prompting the user accordingly. Finally, as the function returns a valid age (as an Integer), I’m casting the returned string as an Integer as the returned value from the function. Adhering to the modular principles of software design, I have a dedicated function tasked with the validation, called from the function responsible for getting and returning the age:

Function GetUserAge() As Integer

    Dim strResponse As String

    Do

        strResponse = InputBox("How old are you?", "Your input is required")

    Loop Until ValidateAge(strResponse)

    GetUserAge = CInt(strResponse)

End Function

Function ValidateAge(strAge As String) As Boolean

    Dim intAge As Integer

    Select Case strAge

        Case vbNullString

            MsgBox "Your age is required in order to proceed", vbOKOnly + vbExclamation, "Mandatory Input"

        Case Else

            If IsNumeric(strAge) Then

                intAge = CInt(strAge)

                ValidateAge = (intAge > 16) And (intAge < 80)

                If Not ValidateAge Then MsgBox "Your age must be between 17 and 79", vbOKOnly + vbExclamation, "Mandatory Input"

            Else

                MsgBox "Your age must be a number", vbOKOnly + vbExclamation, "Mandatory Input"

            End If

    End Select

End Function

This was just one way for validating user input. Another approach would be to make use of the provided input with error handling (as I explain and demonstrate here), but to me it would be less elegant in most cases.

If you found this Blog post helpful, do share it with others – they will thank you!