VBA InputBox function is uses to display a prompt to the users to enter a value. Input Box displays dialog box with a text field, OK and Cancel buttons. And waits for the user to enter and click a button. User can enter a value in the Text Field and press the OK button to submit to the macro.
We have two types of Input Boxes in VBA. The first one is the VBA Input Box Function. And the Second one is the VBA Apllication.InputBox Method. Input Box Function is useful to accept the simple string or numeric values with Ok Cancel Buttons. And the Application.InuptBox method have the advanced option to customize the Input Box.
In this topic:
VBA InputBox Function
VBA InputBox Function helps to accept the string or numeric value from the user. We can pass the Prompt to display in the message box, Title of the dialog box, default value in the input field and set the x,y potions to set the alignment of the input dialog box window.
InputBox Syntax
Following is the syntax of VBA InputBox Function. Prompt is the required parameter and all other are optional.
InputBox( prompt [,title] [,default] [,xpos] [,ypos] [, helpfile, context])
InputBox Parameters
InputBox Function takes the following named arguments. We can pass the values to the Input Box Function based on our requirement. Prompt is a require argument, we can enter a string to display in Input Box body area. Function will process the given arguments values and shows the pop up box.
- prompt: Prompt is a message to display in the Text Box. This describe the purpose of the Input Box. You can enter a question or statement to explain the need of input. User can enter a maximum of 1024 characters as a prompt. Prompt is the required Parameter.
- title: Title is an optional parameter to display in the title bar of the input box window. If the title is left blank, the application name is displayed in the title bar.
- default: A Default Value or a String to display in text box filed. It is an optional parameter.
- xpos: XPos helps to set the Horizontal Placement of Input box Window. It is an optional parameter. If left blank, the input box window is horizontally center aligned.
- ypos: YPos helps to set the Vertical Placement of Input box Window. It is an optional parameter. If left blank, the input box window is Vertically center aligned.
- helpfile: helpfile parameter is useful to set the context-sensitive identifier. This is an optional parameter, context parameter is required when you provide helpfile parameter.
- context: to prove the context number set to the given help topic. This is an optional parameter, helpfile parameter is required when you provide context parameter.
VBA InputBox Uses:
InputBox is used to quickly ask a value to the user at run-time and run the reaming code based on the user input. It is very helpful to quickly get the input from user and perform the calculations based on the user entry.
- Display a Prompt and Accept the Values from Users
- We can set the default values to show the sample string or number to enter in the text box
- Assign to a variable and process the remaining steps
- Quickly accept the user choose and execute the macros based on user preferences
- Perform quick calculations on run-time
VBA InputBox Examples
Here are the most useful VBA InputBox examples. Input box is very useful to accept the values from the user and process the values to produce the results.
Example 1 – InputBox to Accept a Value from User: The following VBA code will display a dialog box and accept the user input. And display it back to the user for confirmation using message box.
Sub VBA_InputBox_Example()
varInput = InputBox("Please enter your name :")
MsgBox "You have entered :" &vbCr &varInput
End Sub
Example 2 – Accept User Input and Perform Calculation: Here is an example to accept a value from the users and perform some arithmetic operations. The following VBA code will asks the user to enter a number and find the square value of the given number. And show the result to the user.
Sub VBA_InputBox_Example_Calculation()
varInput = InputBox("Please Enter a number to find it's Square Value:")
MsgBox "Square Value of given Number is: " & varInput * varInput
End Sub
Example 3 – InputBox with Default Value: We can show the InputBox with a default value. It helps the user to understand the example value in the Input field. The following example ask the user to enter a date to process the records in a certain format.
Sub VBA_InputBox_Example_Default_Value()
varInput = InputBox("Please Enter a Date ( in 'dd-mmm-yy' format) to Process the Records:", "Calendar Date to Process the Records", Format(Date, "dd-mmm-yy"))
'You can write the remaining statemnts to process based on the user input
End Sub
Example 4 – Validate User Input: We can ask the user to enter a number and validate if the input value is a valid number. If user enters a correct value then procede the remaining statements. If user enters an incorrect value then show the Input Box again to the user and ask to enter valid value until user enters the correct value.
Sub VBA_InputBox_DataValidation()
askUser:
varInput = InputBox("Please Enter a week number (1-53) to Process the Records:", "Enter Week Number", Default:=1)
'Validation
'Check if the user enters a number
If Not IsNumeric(varInput) Then
MsgBox "Please Enter Only Numeric Value"
GoTo askUser
End If
'check if the user enter the value in the given range
If Not (varInput >= 1 And varInput <= 53) Then
MsgBox "Please Enter a Numeric Value between 1 and 53"
GoTo askUser
End If
'Successfully passed the validation
MsgBox "Correct!,You have entered the valid information"
'You can write the statemnts to process the valid data
End Sub
VBA Application.InputBox Method
Application.InputBox method is useful to display the Input Box by specifying the Type of the Data to be accepted from the Users. It comes with an additional Parameter ‘Type’, we can pass this named argument to change the Input box with Type Options to suite our requirement.
InputBox Method Syntax
Application.InputBox (Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)
InputBox Method Parameters
- Prompt: Prompt is the text to show in the InputBox. It is the required parameter. User can display maximum of 255 characters.
- Title: Title is an option Parameter. Title is the test to show in the title bar of the InputBox window.
- Default: Optional Parameter to set the default value in the input text box Filed of the input box.
- Left: Optional Parameter to set the left or horizontal position of the Input box window.
- Top: Optional Parameter to set the top or Vertical position of the Input box window.
- HelpFile: To set a related help file..
- HelpContextID: Tells the position in the help file. The context ID number of the Help topic in Help File.
- Type: Specifies the type of value that will be returned. If this parameter is not used then the return type is text.
InputBox Method Options
Here are the list of Options available to set the InputBox Type. You can pass any of these values to the Type argument. If it is left blank, it will considers as the default type string.
| Value | Type |
|---|---|
| 0 | Formula |
| 1 | Number |
| 2 | String |
| 4 | Boolean – True or False |
| 8 | Range |
| 16 | Error value like #Value, #N/A |
| 64 | Array of values |
InputBox Method Examples
Here the list of example on VBA Input Box. These are the frequently used examples in the real-time projects. VBA users can copy the code and use in the code modules.
Example 5 – Accept Only Numbers: We set the Type option to 1 to accept only numbers. VBA will repeatedly prompt the user until entering a valid number.
Sub VBA_Application_InputBox_AcceptOnlyNumbers()
varInput = Application.InputBox("Please Enter a number:", "Enter Week Number", Type:=1)
End Sub
Example 6 – Accept a Boolean: It is very helpful option to accept Boolean value True or False.
Sub VBA_Application_InputBox_AcceptBoolean()
varInput = Application.InputBox("Please Enter True/False:", "Enter Week Number", Type:=4)
End Sub
Example 7 – Accept a Range: Most of the VBA developers uses Application InputBox to select a Cell or Range of Cells. Here are the examples to select the Ranges using InputBox.
Sub VBA_Application_InputBox_SelectACell()
'Activate a Sheet
Sheets(1).Activate
'Now asks the user to select a Cell
Set rngCell = Application.InputBox( _
prompt:="Select a Cell", Type:=8)
MsgBox rngCell.Address
End Sub
Example 8 – Restrict to Select Only One Cell: Sometimes, user required to restrict to select a single Cell. We can check the number of cells selected by user and ask the user to select only single Cell.
Sub VBA_Application_InputBox_LimitToSelectOneCell()
'Activate a Sheet
Sheets(1).Activate
askUser:
Set rngCell = Application.InputBox( _
prompt:="Select a Cell", Type:=8)
If rngCell.Count > 1 Then
MsgBox "Please select only one Cell"
GoTo askUser
End If
MsgBox rngCell.Address
End Sub
Example 9 – At least 3 Cells : We can also set the Input Box to ask the users to select at least n number of cells .
Sub VBA_Application_InputBox_nCells()
'Activate a Sheet
Sheets(1).Activate
askUser:
Set rngCell = Application.InputBox( _
prompt:="Select 3 Cells", Type:=8)
If Not (rngCell.Count = 3) Then
MsgBox "You have Selected " & rngCell.Count & " Cells" & vbCr & "Please select 3 Cells"
GoTo askUser
End If
MsgBox rngCell.Address
End Sub
Example 10 – Pass Input Range as Argument to a Function or Procedure : We can also ask user to select a range to process and pass to a function or procedure.
Sub VBA_Application_InputBox_PassToFunctionProcedure()
'Activate a Sheet
Sheets(1).Activate
askUser:
Set rngCell = Application.InputBox( _
prompt:="Select 3 Cells", Type:=8)
If Not (fnCountCells(rngCell) = 3) Then
sbShowCountOfCells (rngCell)
GoTo askUser
End If
MsgBox rngCell.Address
End Sub
Read More:
VBA Inputbox Method
You can refer the following link for the step by step instructions.
Instructions to run VBA Macro Code
Other Useful Resources:
Click on the following links of the useful resources. These helps to learn and gain more knowledge.
VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables
VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog