MODULES▼
LESSONS▼
TOPICS▼
An input box is a dialog box that allows a user to enter a value which your code can capture and make use of. There are two types of input box you can use in Excel VBA: a generic VBA input box; and an Excel-specific version. This section shows you how to work with the former type.

A basic VBA input box allows the user to type in a value.
Files Needed
You don't need any files for this part of the lesson.
Completed Code
You can click here to download a workbook containing the completed code.
You can ask for basic text input from the user with the InputBox function. In a new workbook, insert a module and create a subroutine called BasicInputBox. Add code to display an input box as shown below:

The InputBox function has seven parameters in total but only the Prompt is required.
Use the Prompt parameter to ask the user a question, or provide an instruction.

Hopefully your users will be capable of following simple instructions such as this one.
You can run the code to display the input box:

It's a little "no frills" but it will suffice for now.
You can customise the Title of an input box in the same way as for a message box:

Here we're using continuation characters (a space and underscore) and named arguments.
Running the code shown above produces an input box similar to this:

Sadly, there's little else you can do to alter the appearance of the input box.
Capturing the Result
Asking for input isn't much use unless you capture what the user has entered!

Unless you store the value typed in, when you click OK your program will instantly forget what your name is (just as I often do after greeting delegates at the start of a training course).
Create a new subroutine called CaptureInputBoxResult.
Finding the Data Type Returned by an Input Box
Before attempting to capture the result of an input box, it's worth finding out what type of value the InputBox function returns. To do this, you can type inputbox followed by a space to display a tooltip:

The tooltip indicates that the type of value returned by the InputBox function is a String.
As the VBA InputBox function returns a string, you could capture its result in a string variable:

Declare a string variable in the subroutine.
Capturing the Result
To return the result of the input box to the variable you have declared, you make the variable equal to the input box:

Assign the input box to the variable. Make sure you enclose the arguments of the input box in a set of parentheses.
Debugging to Check the Result
You can step through the procedure using the F8 key and enter a value when the input box appears:

You don't have to take the instruction quite so literally.
After clicking OK, you can use the Locals window to see that the value has been captured in the variable:

The value you have entered appears in the Locals window.
Using the Result of the Input Box
You can now use the value you have captured in other parts of the procedure:

This simple procedure displays the value that was entered back to the user.
After running the procedure, typing in a value and clicking OK you'll see a message box appear:

The message simply displays the value you typed into the input box.
To practise displaying input boxes:
- Using any workbook, create a new module and add a subroutine called AskMeAnything.
- Add code to display an input box which asks the user an important question:
Sub AskMeAnything()
InputBox _
"What's the best pizza topping?"
End Sub
- Alter the input box code so that it displays a customised title:
Sub AskMeAnything()
InputBox _
"What's the best pizza topping?", _
"Best Pizza"
End Sub
- Run the subroutine to check the appearance of the input box:

Try entering a value and clicking OK. Your code won't capture the value you entered yet, but we'll get to that soon!
- Add a second input box which asks a related question:
Sub AskMeAnything()
InputBox _
"What's the best pizza topping?", _
"Best Pizza"
InputBox _
"What's the worst pizza topping?", _
"Worst Pizza"
End Sub
- Run the subroutine again and check that the second input box appears after you have closed the first one:

Yuck.
- Save and close the workbook.
To practise capturing basic text input:
- Using any workbook, insert a module and create a subroutine called HelloYou.
- Declare a variable which can hold a string value:
Sub HelloYou()
Dim YourName As String
End Sub
- Add code to display an input box which asks the user their name. Store the result of the input box in the variable you have declared:
Sub HelloYou()
Dim YourName As String
YourName = InputBox( _
"What's your name?")
End Sub
- Display a message which greets the user with the name they have entered:
Sub HelloYou()
Dim YourName As String
YourName = InputBox( _
"What's your name?")
MsgBox "Hello " & YourName
End Sub
- Run the subroutine to check that you see a sensible message:

Your message will only be as sensible as the name you type in, of course.
- Add a second variable to hold the user's title. Use another input box to populate this variable and then concatenate its value into the final message:
Sub HelloYou()
Dim YourName As String
Dim YourTitle As String
YourName = InputBox( _
"What's your name?")
YourTitle = InputBox( _
"Enter your title")
MsgBox "Hello " & YourTitle & " " & YourName
End Sub
- Run the subroutine and enter some appropriate values:

You could enter something less extravagant.
- Save and close the workbook.