VBA - Application.InputBox

We saw about InputBox function in our previous post. We learnt that you cannot restrict user to enter a particular type of values in InputBox. Of course, you can write couple of lines of code to validate user entry, but wouldn’t it be nice if your InputBox itself does that validation for you?

Application.InputBox method is at your rescue. Let’s see what it is and how to use it.

Application.InputBox calls a method of the Application object. A plain entry of InputBox in the code calls a function of InputBox that we discussed in our previous post.

Application.InputBox is a powerful tool to restrict the users to enter particular type of values. It also gives more flexibility to choose from various types of values that normal InputBox cannot. For example: you can let user select a range using Application.Input which is simply not possible with standard InputBox.

Also, returned value form the Application.InputBox method is a variant as opposed to a text string that is returned by plain InputBox. A variant can represent any kind of variable data type we discussed in post on varaibles. For example: it can hold a text string, a formula, a Boolean value, a complete Range reference and an array etc.

Syntax

Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)

Application.InputBox: This is the key word to call this method.

Prompt: Mandatory. Whatever you enter in this argument will be displayed to the user. If you are entering a string in prompt, you will have to enclose that string with double quotes. You can also pass one of the variable values as an argument for this.

Title: Optional. This is the text shown on top of the message box. If omitted, “Input” will be shown. Notice the difference. In InputBox, if this argument is omitted “Microsoft Excel” is shown.

Default: Optional. This is shown as default value in the InputBox.

Left: Optional. Specifies an x position for the dialog box in relation to the upper-left corner of the screen

Tops: Optional. Specifies a y position for the dialog box in relation to the upper-left corner of the screen

Helpfile: Optional. If you notice the alert produced by Excel will have either a help button or a question mark button on the alert. Those are to display a help file that is saved with the program. If you have a help file created for your message box, you can provide that here. But creating a help file deserves multiple separate posts but that’s not our concerns at this point of time. You can conveniently ignore this.

HelpContextID: Optional. It is the number of the topic to be displayed from helpfile. If this is provided, helpfile is mandatory (quite obvious)

Type: This is where it is way more powerful than plain InputBox function. Type specifies the return data type. If this argument is omitted or number 2 is entered, the dialog box returns text (which replicates plain InputBox function). You have below options to pass on for this argument.

Notes:

• It is mandatory to assign the Application.InputBox function to a variable if you are using more than one argument
• If you want to omit any argument and move to next one, you should include a comma
• You can allow different types of data input by combining the values with plus sign. For example: if you want to allow numbers and text too in a box, your Type argument would be 1+2.

Let’s see this in action by few examples.

Example 1: Display a message to the user asking to enter a number between 1 and 10.

Sub Test()
Application.InputBox("Enter a number between 1 and 10")
End Sub

Running the code displays below box. This is just like InputBox in operation. You are not restricting any user entry.

Notice the prompt “Input” and placement of buttons at the bottom. This is different if you compare with InputBox dialogue (below picture is copied from InputBox post)

Example 2: Display a message to the user asking to enter a number between 1 and 10. With number 10 entered already in the box that will be displayed.

Sub Test()
MyInput = Application.InputBox("Enter a number between 1 and 10", , 10)
End Sub

Notice the code: Since I am using more than one argument, I assigned Application.InputBox to MyInput variable. As third argument, I entered number 10 which is what I wanted in the box.

Executing code above will display below box.

Number 10 pre-entered in the box will help user if that is what commonly used. It reduces few key presses from the user. If user wants to enter a different number, he can delete existing and enter whatever he wants.

Example 3: Let user select a range of cells.

Sub Test()
MyInput = Application.InputBox("Select a range", , , , , , 8)
End Sub

Running the above code would show up a box and you can click on any cell or cells in Excel, address of that cells is reflected in dialogue box like a formula as shown below.

Example 4: With the above settings, restrict user to enter only numbers.

Sub Test()
MyInput = Application.InputBox("Enter a number between 1 and 10", , 10, , , , , 1)
End Sub

Notice the commas; I am skipping the arguments by placing a comma for each argument. For last argument, I entered number 1 which represents the number Type. After executing this code, a dialogue box just like above will be displayed. However, if the user enters anything other than a number, it will show an error. This process continues until user clicks Cancel or enters a number.

Below is the error displayed if I enter some text in this box and click OK.

Values returned by Application.InputBox can be used just like how we used InputBox returned values. For example, you can let user select few cells and apply formatting to those cells only or ask user to enter a number (let’s call it n) and highlight every n^th row in the sheet etc.

We can see Application.InputBox in action while we move ahead in our learning and start writing complex codes to achieve big tasks. Till then, happy learning!!