The VBA InputBox is a very useful VBA user input feature along the VBA Message Box, before needing to reach out for VBA UserForms The InputBox allows you to create a simple pop-up message with a single user input textbox.
InputBox function definition
Let us start with the InputBox function definition:
InputBox( Prompt, [Title], [Default], [XPos], [YPos], [HelpFile], [Context])
As noticed above the InputBox function requires, as a minimum, only the Prompt parameter to execute. What do the parameters stand for?
- Prompt – A string explaining what kind of input is needed from the user. This message appears inside the InputBox above the textbox for user input.
- Title – Optional. The title of the entire InputBox.
- Default – Optional. The default value of the InputBox. This value will appear by default in the user input textbox.
- XPos and YPos – Optional. These parameters specify the x and y positions for the input dialog box in relation to the upper-left corner of the screen, in points.
- HelpFile – Optional. The name of the Help file for the inputbox. If HelpFile and Context parameters are present, a Help button will appear in the upper-right hand of the dialog box.
- Context – Optional. The context ID number of the Help topic in the provided HelpFile.
The VBA InputBox is a modal dialog box. This means it must be closed or hidden before you can continue working in VBA, Excel, PowerPoint or any other application.
The VBA InputBox return the value input by the user in the input textbox.
Time to explore some code examples
InputBox "How old are you"?
Now let’s add a Title and a Default value.
InputBox "Shall we continue? (yes/no)", "Continue or end", "yes"
Notice that if you want to request specific input from the user you need to suggest (e.g. in brackets) the available options.
Get value provided by user
The function is modal hence prevents further execution until the user provides the required value. Getting the provided value is easy as it is returned by the InputBox function:
Dim val As Long val = InputBox("How old are you?")
Resolving errors / incorrect input
The VBA InputBox does not prevent the user from providing random / junk values so you need to handle validation yourself. I suggest a simple InputBox validation loop as such:
Dim val As String Do Until IsNumeric(val) val = InputBox("How old are you?") Loop 'Success Debug.Print "You are " & val " years old"
Use the VBA InputBox function instead of custom VBA UserForms whenever possible. Why reinvent the wheel? Similarly you can stretch/customize the VBA Message Box.