Introduction | Example | Tutorial | Applications | Comments
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:
- Ability to specify the returned data type. Rather than always returning a string, Application.InputBox lets you specify the returned user input data type.
- 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.
- 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
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.
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
If the user clicks OK without first typing something into the InputBox, clicks Cancel or the red X in the top right corner,
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:
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:="[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
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. What are you waiting for? You’ll love the great VBA content I send your way!This article was written by Michael H. Sorensen on behalf of The VBA Tutorials Blog.
About The VBA Tutorials Blog
The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.Follow