How to Use Excel VBA InputBox with Example

Get FREE Advanced Excel Exercises with Solutions!

In many situations, while working in Excel VBA, we need to ask the user to insert some parameters directly. We can do that using VBA InputBox. If you are wondering how to create and use VBA InputBox, you have come to the right place. In this article, I am going to show you how we can use VBA InputBox in different situations to insert values in Excel with multiple examples. I will also cover the ways of creating Excel VBA InputBox with Example. So let’s get started.


How to Launch VBA Editor in Excel

In this section, we are going to demonstrate how to launch VBA Editor & create a VBA module in Excel. First, you need the Developer tab to display on your ribbon. If you don’t have that, you can look for it in how to enable the Developer tab on your ribbon.

  • First, we go to the Developer
  • Then we will select Visual Basic.

Going to the developer option to select Visual basic for creating Macro

Then a new window will pop up. Then we are going to follow these steps,

  • First, we will select the Insert command.
  • Then we are going to select Module.
  • A new Module will be created.

How to Create a Module in Excel


What Is an InputBox in Excel VBA?

InputBox is a very useful feature in Excel VBA that prompts a dialog box in Excel asking for the user’s input. We can also specify the type of data that the user needs to input. A typical InputBox in Excel looks like this.

An InputBox in Excel

In Excel VBA, there are two ways of creating an InputBox. They are discussed below.


i. Create InputBox with VBA InputBox Function

We can create an InputBox with VBA InputBox Function. The syntax of the VBA InputBox function is given below.

Syntax of VBA InputBox Function

The details of the above arguments are given below.

Argument Required/Optional Value
Prompt Required This should be a string that will be displayed as a message in the dialogue box.
Title Optional This is a string that will be displayed as a Title on the top left of the Dialog box. If omitted, the default value is Microsoft Excel.
Default Optional This string expression will be displayed in the text box as the default response If no other input is provided.
XPos Optional This is the numerical expression in twips that specify the horizontal position of the left edge of the dialog box.
YPos Optional This is the numerical expression in twips that specify the vertical position of the left edge of the dialog box.
HelpFile Optional This is a string-type expression that specifies which Help file should be used to give the dialog box with context-sensitive help. The Context must be provided if a helpfile is given.
Context Optional This is a numeric expression of the Help context number assigned to the appropriate Help topic by the Help author.

The inserted value in the InputBox must be assigned to a variable.


ii. Create InputBox with Application.InputBox Method

The second way of creating InputBox in VBA is by using the Application.InputBox Method. The syntax of this method is given below.

Syntax of  Application.InputBox

The details of the above arguments are given below.

Argument Required/Optional Value
Prompt Required This should be a string that will be displayed as a message in the dialog box.
Title Optional This data will be displayed as a Title on the top left of the Dialog box. If omitted, the default value is Input.
Default Optional This expression will be displayed initially in the text box as the default response.
Left Optional This is the numerical expression in points that specify the x position of the left edge of the dialogue box with respect to the upper-left corner of the screen.
Top Optional This is the numerical expression in points that specify the y position of the left edge of the dialogue box with respect to the upper left corner of the screen.
HelpFile Optional This is a string-type expression that specifies which Help file should be used to give the dialog box with context-sensitive help. The context must be provided if a helpfile is given. If the HelpFile and HelpContextID arguments are given, a Help button will be displayed in the dialog box.
HelpContextID Optional The context ID number of the Help topic in HelpFile.
Type Optional This is numerical data that specifies the return data type. If this argument is not provided, the dialogue box displays text.

The values for different data types for the Type argument are given below.

Value Description
0 A Formula
1 A number
2 String
4 A logical value
8 A cell reference as a Range Object
16 An error value such as #Value
64 An array of Values

Excel VBA InputBox with Example: 7 Useful Cases

In this section, I will demonstrate N examples of using VBA InputBox in Excel with appropriate illustrations. So let’s explore the examples one by one.

1. Using InputBox to Display Input Value

Here, in this example, I am giving a sample Excel VBA code that will take input from the user using an InputBox and return the value with MsgBox.

Code for Using InputBox to Display Input Value

Sub InputBox_function()
Inserted_Value = InputBox("Type something here")
MsgBox ("You have typed " & Inserted_Value)
End Sub

How Does the Code Work?

Inserted_Value = InputBox("Type something here")
  • Here, I have assigned the inserted value in the InputBox to a variable named Inserted_Value. As a result, the input by the user will be stored in Inserted_Value.
MsgBox ("You have typed " & Inserted_Value)
  • A MsgBox will display the message “You have typed ” & Inserted_Value.

If I run the code, an InputBox will pop up asking “Type something here”.

Prompted InputBox asking for Input

Then I typed 120 and clicked on the OK button.

Typing Input Value in InputBox

As a result, it displayed the result “You have typed 120”

Displaying Input Value in a MsgBox


2. Using InputBox to Calculate Within Excel

Here, in this example, we have prompted the user to insert the length and width of a rectangle to calculate the area in Excel. Then the area is displayed using MsgBox. The VBA code is given below.

VBA Code for Calculating  Rectangle Area using InputBox

Sub find_Rectangle_Area()
Dim Rect_Length As Double
Dim Rect_Width As Double
Rect_Length = InputBox("Enter Length of the Rectangle ", "Enter a Number")
Rect_Width = InputBox("Enter Width of the Rectangle", "Enter a Number")
Area = Rect_Length * Rect_Width
MsgBox "Area of the Rectangle = " & Area
End Sub

How Does the Code Work?

Rect_Length = InputBox("Enter Length of the Rectangle ", "Enter a Number")
  • An InputBox will pop up asking to enter the length of the rectangle and this value will be stored in the variable named Rect_Length 
Rect_Width = InputBox("Enter Width of the Rectangle", "Enter a Number")
  • An InputBox will pop up asking to enter the with of the rectangle and this value will be stored in the variable named Rect_Width 
Area = Rect_Length * Rect_Width
  • The area of the rectangle will be calculated by multiplying the two variables Rect_Length  & Rect_Width.
MsgBox "Area of the Rectangle = " & Area
  • A MsgBox will display the area of the rectangle.

Now if we run the VBA code, it will first ask us for entering the length of the rectangle. I typed 20 and clicked OK.

InputBox asking for entering the length of Rectangle

Next, it will ask for the Width of the Rectangle. Here I typed 30 and clicked OK.

InputBox asking for entering the width of Rectangle

As a result, a MsgBox will display the area of the rectangle.

MsgBox showing Area of Rectangle


3. Validating User Input from InputBox

Here, we have shown another example where the user is asked to enter his/her birthday using InputBox in Excel. The VBA code checks whether the inserted value’s data type is a date or not. If the data type is not a date type, the user is asked to insert his/her birthday again. The code that we used here is below.

VBA Code for  Using InputBox to Validate User Input

Sub CheckBirthday()
Dim birth_day As Variant
Dim valid_Date As Boolean
Do While Not valid_Date 'Repeat loop until a valid date is entered
birth_day = InputBox("Please enter your birthday (MM/DD/YYYY):")
If isDate(birth_day) Then
valid_Date = True
Else
MsgBox "The input value is not a valid date. Please try again."
End If
Loop
MsgBox "Your birthday is " & birth_day & "."
End Sub

How Does the Code Work?

birth_day = InputBox("Please enter your birthday (MM/DD/YYYY):")
  • An InputBox will open up asking to enter the user’s birthday. This data will be stored in a variable named birth_day.
If isDate(birth_day) Then
            valid_Date = True
  • The data that is stored in the variable birth_day will be checked whether it is a valid date or not using isDate Function. If the variable birth_day  is a valid date then another Boolean type variable named valid_Date will be made True.
Else
  MsgBox "The input value is not a valid date. Please try again."
  • If the birth_day is not a date then a MsgBox will be displayed saying “The input value is not a valid date. Please try again.”
Do While Not valid_Date…….Loop
  • This loop will continue until valid_Date turns True.
MsgBox "Your birthday is " & birth_day & "."
  • When the Do While loop breaks, a MsgBox will open and will display the inserted birthday of the user.

Now if we run the VBA code, an InputBox will open up asking for the user’s birthday.

An InputBox asking for Birthday

Now at first, I inserted a value that is not a date.

Entering a value in InputBox which is not a Date

As a result, it will show me a MsgBox saying to try again.

A MsgBox showing that my input is not a valid date

Next, I inserted a date value in the InputBox.

Inserting Birthday in InputBox

As a result, it was accepted and the date value was returned in a MsgBox.

A MsgBox showing inserted date

Read More: Excel VBA: Create InputBox with Multiple Inputs


4. Create InputBox with Multiple Lines

If we want to display the prompt message in multiple lines, we can do that using the vbNewLine command. Below is a sample VBA code example in Excel where we have created an InputBox with Multiple lines as Prompt text.

VBA Code to Create InputBox with Multiple Lines

Sub Multiple_Line_InputBox()
Input_value = InputBox("This is the 1st line." & vbNewLine & "This is the 2nd Line")
End Sub

How Does the Code Work?

Input_value = InputBox("This is the 1st line." & vbNewLine & "This is the 2nd Line")
  • An InputBox will be displayed where This is the 1st line will be displayed at the top and This is the 2nd Line will be displayed at the bottom in the prompt section of this InputBox.

Now if we run the code, an InputBox will open which will contain 2 lines of Prompt text.

An InputBox with Multiple Lines as prompt text

Read More:Excel VBA InputBox with Multiple Lines


5. Creating InputBox That Interacts with Cancel Button in Excel VBA

In this example, we tried to interact with the Cancel command of InputBox in Excel. Here, we will use the following VBA code to ask the user to insert a value in the InputBox.

VBA Code for Creating Customized Dialog Boxes Using InputBox

Sub InteractingCancel()
Dim input_value As String
input_value = InputBox(Prompt:="Enter a value")
If StrPtr(input_value) = 0 Then
MsgBox "You clicked the Cancel button"
ElseIf input_value = "" Then
MsgBox "You didn't enter anything"
Else
MsgBox "Your entered " & input_value
End If
End Sub

How Does the Code Work?

input_value = InputBox(Prompt:="Enter a value")
  • An InputBox will open asking the user to enter a value and this value will be stored in a variable named input_value.
If StrPtr(input_value) = 0 Then
            MsgBox "You clicked the Cancel button"
  • The function StrPtr will check whether the input_value is a Nulll string or not. if it is a Nulll string that means the user has clicked the Cancel button without inserting any data. Then, a MsgBox will display You clicked the Cancel button.
ElseIf input_value = "" Then
 MsgBox "You didn't enter anything"
  • If the input_value value is an Empty string then a MsgBox will show “You didn’t enter anything”
Else
MsgBox "Your entered " & input_value
  • Otherwise, the message box will show the inserted value 

Now if we run the code, an InputBox will pop up.

An InputBox was asking for value

Now If you click OK without inserting any value then a MsgBox will be displayed saying that You didn’t enter anything.

A MsgBox showing that You didn't enter anything

Now again run the code and press the Cancel Button.

Clicking Cancel Button of An InputBox

As a result, it will show another MsgBox saying that You clicked the Cancel Button.

A MsgBox showing that You clicked the Cancel Button

Lastly, again run the code and insert 12.

Inserting a Value in An InputBox

As a result, another MsgBox will open up saying that you have entered 12.

A MsgBox showing that You Entered 12

Read More: How to Handle VBA InputBox Cancel Button in Excel


6. Creating Customized Dialog Boxes Using InputBox

We can also create a custom dialog box using InputBox in Excel VBA. In this example, we will offer the user to choose any of three flavors of ice cream and based on the choice how many ice creams he/she wants to buy. Finally, we will display which flavors of ice cream the user chose and how many he/she wanted to buy.

VBA Code for Creating Customized Dialog Boxes Using InputBox

Sub CustomDialogBox()
Dim product As String
Dim quantity As Integer
product = InputBox("Please select a flavor of Ice Cream :", "Flavor Selection", "Mango,Strawberry,Banana")
Select Case product
Case "Mango"
quantity = InputBox("Please enter the quantity of Mango flavored ice cream you want to purchase:" _
, "Mango flavored ice cream Quantity", 1)
Case "Strawberry"
quantity = InputBox("Please enter the quantity of Strawberry flavored ice cream you want to purchase:" _
, "Strawberry flavored ice cream Quantity", 1)
Case "Banana"
quantity = InputBox("Please enter the quantity of Banana flavored ice cream you want to purchase:", _
"Banana flavored ice cream Quantity", 1)
Case Else
MsgBox "Invalid product selection!"
Exit Sub
End Select
MsgBox "You have selected " & quantity & " units of " & product & "flavored ice cream(s)"
End Sub

How Does the Code Work?

product = InputBox("Please select a flavor of Ice Cream :", "Flavor Selection", "Mango,Strawberry,Banana")
  • This will prompt an InputBox which will ask the user to select a flavor of ice cream. The default value is Mango, Strawberry, or Banana
Select Case product
  • This will select different cases for different selections.
Case "Mango"
quantity = InputBox("Please enter the quantity of Mango flavored ice cream you want to purchase:", "Mango flavored ice cream Quantity", 1)
  • If the case is Mango then another input box will open up and ask for the quantity of mango-flavored ice cream. This is true for the other 2 cases as well.
Case Else
MsgBox "Invalid product selection!"
Exit Sub
  • If any other value is inserted in the first InputBox other than the mentioned three flavors then a MsgBox will open up showing an Invalid product selection!
MsgBox "You have selected " & quantity & " units of " & product & "flavored ice cream(s)"
  • A message box will open up and display the selected flavor and the number of units of that flavored ice cream

Now if you run the code, an InputBox will open up and will say “Please, select a flavor of ice cream: “. From the default 3 values, you have to insert any of the three.

A InputBox asking for selecting a Flavor of Ice cream

Suppose, we inserted strawberry.

A InputBox asking for selecting a Flavor of Ice cream

Now another customized InputBox will open up asking for the number of quantities of that flavor of ice cream. Here, I inserted 12.

A InputBox asking for the number of quantities of the specific flavored ice cream

As a result, a MsgBox will display “You have selected 12 units of Strawberry flavored ice cream(s)”.

A MsgBox showing  “You have selected 12 units of Strawberry flavored ice cream(s


7. Using Application.InputBox Method to Take Range as Input

We can use Application.InputBox Method to take range as Input. Below example, a VBA code is given where the user will be asked to select a range in Excel and in return, a MsgBox will show the address of the selected range.

VBA Code for selecting Range as Input

Sub Application_InputBox_Method()
Set Inserted_range = Application.InputBox("Select a Range", Type:=8)
MsgBox ("You selected " & Inserted_range.Address)
End Sub

How Does the Code Work?

Set Inserted_range = Application.InputBox("Select a Range", Type:=8)
  • An input box will open up. As the Type is assigned 8, it will only accept a range type data. and this value will be stored in a variable named inserted_range.
MsgBox ("You selected " & Inserted_range.Address)
  • A MsgBox will open and display the address of the selected range.

Now, if we run the code, an InputBox will open up asking for Selecting a Range.

An InputBox asking for Selecting a Range

Now, if we select a range B5:C11.

Selecting range B5:C11

As a result, a MsgBox will show the address of the selected range in Excel.

A MsgBox showing the address of the selected range


Things to Remember

  • The inserted value in InputBox must be assigned to a variable for further use.
  • While assigning InputBox to a variable, you can declare the data type of the variable as Hence, it will be able to hold any datatype value.
  • You can only insert a maximum of 255 characters in the InputBox and it can only return 254 characters.

Frequently Asked Question

1. What is an InputBox in VBA?

An InputBox is a built-in VBA function that displays a dialog box that prompts the user to enter a value.

2. How do I use an InputBox in VBA?

You can use the syntax shown below in VBA to use an input box: InputBox(Prompt, [Title], [Default], [XPos], [YPos]) (Prompt, [Title], [Default], [XPos], [YPos]). Prompt is the message that appears in the input box, Title is the name of the input box, Default is the shown default value, and XPos and YPos are the input box’s x and y coordinates.

3. What data types can an InputBox return?

An InputBox can return values of several data types, including text, numbers, dates, and Boolean values.

4. How can I validate the user input in an InputBox?

You can use conditional statements or functions like IsNumeric(), IsDate(), or IsNumeric to validate the user input in an InputBox ()

5. Can I customize the appearance of an InputBox?

Absolutely, by defining the title, default value, and position of the input box, you can modify the way an InputBox looks. With user forms, you can also design your own unique input fields.

6. Can I use an InputBox to prompt the user to select a range in Excel?

Sure, you can ask the user to choose a range using Excel VBA’s Application.InputBox() method. You can use this method to specify the kind of input you require, such as a range or a formula.

7. Can I use an InputBox to prompt the user to select a file?

Sure, you can ask the user to choose a file using Excel VBA’s Application.FileDialog() method. You can choose the specific type of file you want to choose using this method, such as a text file or an Excel workbook.

8. How do I handle errors in an InputBox?

You can use error handling strategies like On Error Resume Next or On Error GoTo to handle errors in an input box. To verify user input and prevent errors, you can also utilize conditional expressions or functions.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

That is the end of this article regarding different Excel VBA InputBox with Example. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Aniruddah Alam
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo