How to use Input Box in VBA

As the name suggests, Input Box is a function that shows an input box to the user and collects a response.

Look at the below example where we have an input box that asks about your name and there’s an input box to enter it. If you look at the title bar which is just like the message bar, we have a title that says, “Enter Name” and the message shows “What’s your name?”.

Below is the syntax of the VBA Input Box:

InputBox(Prompt[Title][Default][Left][Top][HelpFile, HelpContextID][Type])

1. Prompt: In the prompt, you can specify the message you want to show in the Input Box. You can use a maximum of 1024 characters in it.
2. [Title]: A string that shows at the title bar of the input box. If you skip the title, the application name will be placed in the title bar.
3. [Default]: That’s the default text that you want to have in the input bar by default to show to the user.
4. [XPos]: A number that represents (position of X-axis) the prompt distance from the left side of the screen horizontally and if left blank, the input box will be horizontally centered.
5. [YPos]: A number that represents (position of Y-axis) the prompt distance from the left side of the screen horizontally and if left blank, the input box will be horizontally centered.
6. [Helpfile]: To specify a help file for the user. The user can access that help file using the help button.
7. [context]: A Numeric expression that identifies the Help context number assigned and if the context is provided, a helpfile must also be provided.

All the arguments that are in the square bracket are optional if you skip specifying any of those, you can still use the input box function, but in most cases, you won’t need to use more than the first three arguments.

How to use Input Box in a VBA Code

1. Type “InputBox” and enter a space and you will get a tool for the arguments you need to define.
2. Specify the “Prompt”, message that you want to show to the user.
3. Define a title for the input box, otherwise, it will show the default title.
4. Mention the text that you want to have in the input bar by default.

And when you run this code, it will show you an input box just like the following:

Example to use VBA Input Box in Excel

You might be wondering how you can use an input box in the real world. In the following example, I have shared a simple but powerful example.

Sub vba_input_box()

'variable to store the count of rows.
Dim iRow As Long

'get the count cells that are filled
iRow = WorksheetFunction.CountA(Range("A:A"))

'get to the next blank cell in column A
Cells(iRow + 1, 1).Select

'enter the value from the input box into the active cell
ActiveCell = InputBox("What is your name?", "Enter Name")

End Sub

The above code enter’s the name in column A one after another. It counts the cells that are filled in the column and moves to the next blank cell. After that, take the value from the input box and enter it there.