VBA - Input Box

Thank you readers for encouraging my posts on Excel. With this post, this blog now has 100 posts and many more to go. Let's spread the knowledge. Cheers!!!!

We saw about MsgBox in one of our previous posts. MsgBox is used to alert user with some information. We can also provide some buttons such as Yes, No, Retry etc for the user to click. According to user response, we can continue with next steps of macro.

In some cases, it might not be enough to just get user click a button. You may have to solicit some information from the user for further action. For example: you may have to get a name from the user and use that name in a subsequent procedure. MsgBox function falls short here as it can only offer buttons to click.

In such cases, InputBox function is your friend. This function is similar to MsgBox with an additional functionality to solicit input from user. InputBox function displays a prompt in a dialog box, waits for the user to input text or click a button, and returns a string containing the contents of the text box.

InputBox syntax

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

InputBox: This is the key word to call Input Box

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. Do you remember the line we used in variables post?

Title: Optional. This is the text shown on top of the input box. If omitted, “Microsoft Excel” will be shown.

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

Xpos: Optional. This is a number that specifies the horizontal distance of the left edge of the dialog box from the left edge of the screen. If xpos is omitted, the dialog box is horizontally centred.

Ypos: Optional. This is a number that specifies, the vertical distance of the upper edge of the dialog box from the top of the screen. If ypos is omitted, the dialog box is vertically positioned approximately one-third of the way down 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 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.

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

Notes:

• It is mandatory to assign the 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.

Let’s see the InputBox in action with few examples.

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

Sub Test()
InputBox ("Hello, please enter a number between 1 and 10")
End Sub

Executing the code above will display a dialogue like below.

In the bottom of the above box, user can enter anything and / or click OK or Cancel buttons. We will see how to work with user response shortly.

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()
Dim MyInput As String
MyInput = InputBox("Hello, please enter a number between 1 and 10", , 10)
End Sub

Notice the code: Since I am using more than one argument, I am declaring a variable MyInput and assigned InputBox to that variable. And I used a comma to skip second argument. As third argument, I entered number 10 which is what I wanted in the box by default.

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.

When the user clicks OK button, whatever is entered in the box will be assigned to the variable. If Cancel button is clicked then our variable get a null string (empty or nothing is represented by “” i.e. two double quotes with nothing in-between)

Problem with InputBox is that you cannot define the buttons you need. OK and Cancel buttons are always displayed.

Work With User Response

InputBox returns user entry as a text string. Using this string; you can further write code that uses it. Let’s extend our example code above to show the user entered value back to him in an MsgBox for confirmation.

Sub Test()
Dim MyInput As String
MyInput = InputBox("Hello, please enter a number between 1 and 10", , 10)
If MyInput = "" Then 'User didn't enter anything or clicked cancel
    MsgBox ("You entered nothing")
Else 'Ok. something is entered
    MsgBox ("You entered" & MyInput & vbCrLf & "Click OK to Proceed")
End If
End Sub

Running above code displays an input box first (refer to above picture). Based on user action, there will be subsequent message boxes.

If user clicked Cancel button or didn’t not enter anything and clicked on OK button, below message is displayed.

If user entered something (not necessarily a number between 1 and 10) and clicked on OK button, below message is displayed. (Of course, with whatever user entered in place of 10)

As mentioned above, InputBox returns anything entered in it as a string i.e. as text. Even if you enter a number 10 as above, you cannot use it in subsequent calculations as a number. If you are sure that only number is entered, multiply the string with 1 in your code. That would force the number in string to be identified as a number.

Sub Test()
MyInput = InputBox("Hello, please enter a number between 1 and 10", , 10) * 1
End Sub

But remember, above code will result into an error if what is entered in the InputBox is not a number.

MsgBox & InputBox are the two basic dialogues used to solicit user’s response. Both have their own limitations, for example in InputBox, you cannot restrict the user to enter only a number. You cannot customize appearance of these boxes etc.

But there are alternatives. If you are looking to restrict what user enters, you can use Application.InputBox (note that what we saw above is a plain InputBox. With Application qualifier, a different function is called altogether). We will see about this in our next post.

You can make your own designed message boxes and input boxes using UserForms. We will see about them eventually. Till then, explore what we discussed on this series till now. Cheerio!!!