VBA InputBox

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.

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()


varInput = InputBox("Please Enter a week number (1-53) to Process the Records:", "Enter Week Number", Default:=1)

'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
'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

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

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

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

Leave a Reply