Use Application.InputBox to prompt for VBA User Input

Introduction - VBA Application.InputBox

Use the Application.InputBox dialog box to prompt for VBA user input. Input boxes are by far the easiest way to interact with a user in VBA, although other options, such as creating a userform, are also available.

The Application.InputBox is the Excel-specific version of the generic InputBox function available for other Microsoft Office applications. The main advantages of using the Application.InputBox function instead of the generic InputBox function are:

1. Ability to specify the returned data type. Rather than always returning a string, Application.InputBox lets you specify the returned user input data type.
2. Built-in data validation. If this optional parameter is set, the user input will be validated and an error message will be displayed if the input doesn’t match the specified data type. In other words, if you request a number and the user inputs a string, the Application.InputBox will automatically validate the data and generate an error.
3. Non-modal window. This means the user can interact with the document in the background and select values from it for input, rather than having to type in everything by hand.

All of these flexible features are particularly useful when working in Excel.

Example - VBA Application.InputBox

Sub displayApplicationInputbox()

    Dim varUserInput As Variant
    
    varUserInput = Application.InputBox("User ID")
    
    ' continue if the user didn't cancel or typed in nothing
    If Not varUserInput = "False" Then
        ' *********************
        ' put your code in here
        ' *********************
    End If

End Sub

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

Tutorial - VBA Application.InputBox

When you run the macro above, the following dialog box will be displayed:

VBA Application.InputBox Prompt

This example prompts for a user ID, but you can prompt for anything you want. The varUserInput variable holds the user input and, since the dialog box can deal with several different data types, it is declared as a variant type variable. However, since the return data type was not explicitly set in this example, the function will in this case always return a string just like the generic InputBox function, regardless of user input. More on this later.

If the user clicks OK without first typing something into the InputBox, clicks Cancel or the red X in the top right corner, varUserInput will be returned as the string False. Because of that, we must check if the user did indeed type something into the input Box. That’s the purpose of the conditional If statement in the example above.

Customizing the InputBox Dialog

Right now our InputBox is rather basic and doesn’t incorporate any of the features which make Application.InputBox a better choice than the generic InputBox. So let’s demonstrate some of these fancy features!

I’ll focus on the following arguments, or properties, since they are the most important ones to understand:

• Title
• Default
• Type

Changing the Title

You can change the title appearing in the upper left of your dialog box from the default “Input” to whatever text you want by setting the Title argument, like this:

Sub displayApplicationInputbox2()

    Dim varUserInput As Variant
    
    varUserInput = Application.InputBox("User ID", "Input user ID")
    
    ' continue if the user didn't cancel
    If Not varUserInput = "False" Then
        ' *********************
        ' put your code in here
        ' *********************
    End If

End Sub

VBA Application.InputBox Custom Title

Changing the default value

This can be useful in a number of ways. You can use it to give the user a bi of a heads up about what type of data you’re looking for. In addition to being able to set the default value as a string, you can also set it to a range object so it automatically displays the value of a specific cell in the document.

Sub displayApplicationInputbox3()

    Dim varUserInput As Variant
    
    varUserInput = Application.InputBox(prompt:="User ID", Default:=Range("A1"))
    
    ' continue if the user didn't cancel
    If Not varUserInput = "False" Then
        ' *********************
        ' put your code in here
        ' *********************
    End If

End Sub

VBA Application.InputBox default value

In the above example, the default value has been set to the value of cell A1 in the active sheet. You can also set it to a fixed value by replacing Default:=Range("A1") with Default:="[insert your default text here]"

Notice the default value of the input box is automatically highlighted, so that the user can easily erase it in a single keystroke. Moreover, we have changed the assignment statements of the arguments to Application.InputBox. Right after the name of the property we want to change, we simply use the assignment operator := to set the value of the property in question. By doing it this way, you don’t have to remember the order of the arguments in the Application.InputBox method.

Setting the return data type

By setting the Type parameter, the built-in data validation ensures that the user input is in fact of the type you want it to be. This is a real time-saver. You no longer have to write line after line of code to perform your own data validation!

You can set the allowed “return data types” to the following values:

• 0 - A formula
• 1 - A number
• 2 - Text (a string)
• 4 - A logical value (True or False)
• 8 - A cell reference, as a Range object
• 16 - An error value, such as #N/A
• 64 - An array of values

It’s very powerful to be able to restrict a users input to numbers only, for example. Your user will still be able to type letters, but the Application.InputBox function will check the data once the user clicks OK and won’t let them proceed until they comply with your data type restriction or they exit the form.

Sub displayApplicationInputbox4()

    Dim varUserInput As Variant
    
    varUserInput = Application.InputBox( _
        prompt:="User ID", _
        Default:="Only numbers please...", _
        Type:=1)
    
    ' continue if the user didn't cancel
    If Not varUserInput = "False" Then
        ' *********************
        ' put your code in here
        ' *********************
    End If

End Sub

VBA Application.InputBox return data type

In the above example, we only allow the user to enter numbers, but you can also set the Type property to more than one at the same time. For instance, if the input box should accept both numbers and text, you simply replace Type:=1 with Type:=1+2.

Note, clearing the input field and clicking OK will generate an error message stating there’s a problem with the formula. This is because the function interprets this input as the user attempting to enter a formula producing a number.

Application Ideas - VBA Application.InputBox

The Application.InputBox function is especially handy when you need to prompt for user input and don’t want to spend time writing convoluted validation code for several return data types. It comes pre-packaged with all the basic features you need for this task, right out of the box. If you ask the user for a number and the user types text, the Application.InputBox method will warn the user for you.

If you haven’t already done so, please subscribe to my free wellsrPRO VBA Training Program using the form below. What are you waiting for? You’ll love the great VBA content I send your way!