Excel VBA: InputBox Type with Examples

Get FREE Advanced Excel Exercises with Solutions!

InputBox is an excellent feature of Excel VBA. Using InputBox, a user can prompt for user input in the form of text, numbers, arrays, and many more formats. InputBox is beneficial in various situations, such as gathering information from the user or performing a calculation based on user input. Today, we will discuss Excel VBA InputBox Type. After reading this article, you will be able to discover all the aspects of InputBox.

Through the following image, you can easily get acquainted with the types of InputBox in Excel VBA. Further, we will describe each type with examples.

Excel vba inputbox type


Overview of Excel VBA InputBox

Using the VBA InputBox() function, we can easily display an InputBox to the viewers and request responses from them. This is especially useful for obtaining a single input from the user.

Syntex for VBA InoutBox Function)

Syntax

The syntax of this function is as follows:

InputBox(Prompt, [Title], [Default], [Xpos], [Ypos], [Helpfile], [Context] )

Parameters or Arguments

Arguments for VBA InputBox)

Return value

InputBox returns value as a variant.


Difference Between InputBox and Application.InputBox in Excel VBA

In the following table, we have described the difference between InputBox and Application.InputBox.

InputBox or VBA InputBox Application.InputBox
It is a standardized VBA function for gathering data from a user. This is an updated version of the generic InputBox.
This InputBox does not allow you to specify the variable type. This enables you to mention the type of the variable, such as number, string, array, etc.
It will not return an error whatever you provide as input. If the variable type does not match the input, then the InputBox will not take the input and will return an error.

How to Create an InputBox with Excel VBA

In the following video, you can see that we have created a VBA InputBox. This InputBox takes any item and shows the item in cell B5. Next, we will describe how you can also create a VBA InputBox.

To create a simple InputBox, you can copy the following VBA code.

Sub VBA_InputBox()
    input_item = InputBox("type an item:")
Range("B5") = input_item
End Sub

After running the code, you have to type any item in the InputBox. Here, we typed “Apple”.

Then, click OK in the InputBox.

Therefore, you can see the item in cell B5.

Here, no matter what you type, a number, array, or text in the InputBox, the InputBox will take the input.

VBA InpuBox with an Item

Creation of Application.InputBox

In the following video, you can see that we have created an Application.InputBox. We defined Type 1 for this InputBox therefore, it only takes number. The number is then shown in cell B5. Next, we will describe how you can also create such Application.InputBox.

To create an Application.InputBox, you can copy the following VBA code.

Sub Application_InputBox()
Input_number = Application.InputBox("type a Number:", Type:=1)
Range("B5") = Input_number
End Sub

After running the code, you have to type a Number in the InputBox.

Then, click OK in the InputBox.

Therefore, you can see the Number in cell B5.

Application.InputBox with a Number in Excel VBA

If we provide a string instead of a number in the InputBox, it will return an error. This is because we have specified the variable type.

See the following picture regarding this.

InputBox Showing an error


Excel VBA: InputBox Type (Based on Value of Data Type)

Application.InputBox has 7 variable types. In the following picture, you can see the type value and description. Further, we will describe examples of each type of InputBox in Excel VBA. Here, we used Excel 365. You can use any available Excel version.

Application.InputBox with a Number in Excel VBA

Note: One should mention the type properly. If the user set a wrong type, then the InputBox will show an error, and it will not take any input. Therefore, you must go through the next part carefully.


1. InputBox with Formula (Type 0)

Type 0 is used for a formula in the Application.InputBox. This is the first type of InputBox in Excel VBA. When a user defines Type 0, then the user must input a formula in the InputBox. In the following video, you can see how Type 0: InputBox with formula works. Using the formula of the InputBox, we have calculated the sum of two cells. Also, we put the calculated result in cell D5. Next, we will describe how you can do the task.

First, we typed the following VBA code in Sheet1.

Sub Type0_formula()
Dim A, B As Integer
A = InputBox("Enter the value of A:")
B = InputBox("Enter the value of B:")
X = Application.InputBox("Type a formula", "Type0", Type:=0)
Range("B5") = A
Range("C5") = B
Range("D5") = X
End Sub

🔎 Code Breakdown:

Sub Type0_formula()
Dim A, B As Integer

We take Type0_formula as the Sub procedure and declare A,B as Integer.

A = InputBox("Enter the value of A:")
B = InputBox("Enter the value of B:")

Two InputBox functions are used to input the value of A and B. And these InputBoxes are assigned to variables A and B.

X = Application.InputBox("Type a formula", "Type0", Type:=0)

Application.InputBox is assigned to variable XType 0 indicates that the InputBox will only take the formula as input. This, this InputBox prompts the user to input a formula.

Range("B5") = A
Range("C5") = B
Range("D5") = X

The value of A will be stored in cell B5.

The value of B will be stored in cell C5.

And the value of X will be stored in cell D5.

End Sub

Finally, we end the Sub procedure.

After that, we Run the code.

Then, we give the value of A and B in the InputBox.

Finally, we type a formula in the Type0 InputBox.

Hence, you can see the result of the formula in cell D5.

Result after using InputBox with Formula

Read More: Excel VBA: Custom Input Box


2. InputBox with Number (Type 1)

In Application. InputBox, Type 1 is used for Number. When a user defines Type 1, then the user must input a number in the InputBox. In the following video, you can see how Type 1: InputBox with number works. Using the number of the InputBox, we will know the Salary of two employees. The result will be shown in cell C5 and C6 respectivelt. Next, we will describe how you can do so.

First of all, we typed the following VBA code in Sheet2.

Sub Type1_Number()
Dim A As Integer
Dim i As Integer
For i = 1 To 2
A = Application.InputBox _
("Type the Salary for Employee ID No" & i, "Type1", Type:=1)
Range("C" & (i + 4)) = A
Next i
End Sub

🔎 Code Breakdown:

Sub Type1_Number()
Dim A As Integer
Dim i As Integer

We take Type1_Number as the Sub procedure and declare A, and i as Integer.

For i = 1 To 2

We used the For..Next loop which will execute twice, once for each employee.

A = Application.InputBox _
("Type the Salary for Employee ID No" & i, "Type1", Type:=1)

Application.InputBox is assigned to variable AType 1 indicates that the InputBox will only take Number as input. Thus, this InputBox prompts the user to input a number.

Range("C" & (i + 4)) = A

The salary value will be stored in the corresponding cell in column C. The row number is determined by the loop iteration count plus 4 (since the first cell to be written to is C5).

Next i

This ends the For loop.

End Sub

Finally, we end the Sub procedure.

After that, we Run the code.

Therefore, you will see an InputBox will prompt up.

Then, we typed 1200 as the Salary for Employee ID No 1 >> click OK.

Therefore, you can see the Salary in cell C5.

Output after using InputBox with a Number

Read More: Excel VBA InputBox with Number Only Criteria


3. InputBox with Text (Type 2)

In Application. InputBox, Type 2 is used for Text or String. When a user defines Type 2, then the user must input a text or string in the InputBox. In the following video, you can see how Type 2: InputBox with text  works. Using the text of the InputBox, we will know the Name of two employees. Next, we will describe how you can do so.

In the first place, we will type the following VBA code in Sheet3.

Sub Type2_String()
Dim A As String
Dim i As Integer
For i = 1 To 2
A = Application.InputBox _
("Type a Name for employee Id No " & i, "Type2", Type:=2)
Range("C" & (i + 4)) = A
Next i
End Sub

🔎 Code Breakdown:

Sub Type2_String()

Takes Type2_String as the Sub procedure.

Dim A As String
Dim i As Integer

Declares A and i as Integer.

For i = 1 To 2

Uses the For loop which will execute twice, once for each employee.

A = Application.InputBox _
("Type a Name for employee Id No " & i, "Type2", Type:=2)

Application.InputBox is assigned to variable AType 2 indicates that the InputBox will only take String as input. Thus, this InputBox prompts the user to input a Text or String.

Range("C" & (i + 4)) = A

The employee name will be stored in the corresponding cell in column C. The row number is determined by the loop iteration count plus 4 (since the first cell to be written to is C5).

Next i

This ends the For loop.

End Sub

Finally, we end the Sub procedure.

After that, we Run the code.

Therefore, you will see an InputBox will prompt up.

Then, we typed Mike as the Name for employee ID No 1 >> click OK.

Therefore, you can see the name Mike in cell C5.

Result after using InputBox with Text or String

Read More: Excel VBA: InputBox Date Format


4. InputBox with a Logical Value (Type 4)

In Application.InputBox, Type 4 is used to check a Logical Value. If the logical value is true, the InputBox will return TRUE, otherwise, it will return FALSE. In the following video, you can see that when we enter a number in the InputBox, it returns TRUE. Since our input number is a logical number, the InputBox returns TRUE. If, however, you input some illogical input, it will return FALSE. Further, we will describe the example to you.

In the first place, we will type the following VBA code in Sheet4.

Sub Type4_logicalValue()
A = InputBox(" enter anything you want")
Range("B5") = A
Input_number = Application.InputBox _
("Enter the previously entered content:", Type:=4)
Range("C5") = Input_number
End Sub

🔎 Code Breakdown:

Sub Type4_logicalValue()

Takes Type4_logicalValue as the Sub procedure.

A = InputBox(" enter anything you want")

This line takes an InputBox to input any value, and we assign that InputBox to A.

Range("B5") = A

Sets B5 to store the input assigned to variable A.

Input_number = Application.InputBox _
("Enter the previously entered content:", Type:=4)

Application.InputBox is assigned to the variable Input_NumberType 4 indicates that the InputBox will show TRUE when the input is logical, otherwise, it will show FALSE.

Range("C5") = Input_number

We stire the result of the InputBox in cell C5.

End Sub

We end the Sub procedure.

After that, we Run the code.

Therefore, you will see an InputBox will prompt up.

Then, we typed 1234 >> click OK.

Therefore, you can see TRUE in cell C5.

Outcome of InputBox with logical value

Read More: Excel VBA: InputBox with Default Value


5. InputBox with Cell Reference/Range (Type 8)

In Application. InputBox, Type 8 is used for Cell Reference. When a user defines Type 8, then the user must input a cell or a range of cells in the InputBox. In the following video, you can see how Type 8: InputBox with Cell Reference  works. Here, ID 101 Details is presented in cells B8:D9. Further, using  Type 8 Application.InputBox, we will find out ID Details, and we will put the address in cell C5.

In the first place, we will type the following VBA code in Sheet5.

Sub type8_range()
Set A = Application.InputBox("select a range", Type:=8)
Range("C5") = A.Address
End Sub

🔎 Code Breakdown:

Sub type8_range()

We take type8_range as the Sub procedure.

Set A = Application.InputBox("select a range", Type:=8)

Application.InputBox is set for variable A.  Type 8 indicates that the InputBox will take cell reference.

Range("C5") = A.Address

. Address method is used to find the address of A, and then the address is stored in cell C5.

End Sub

We end the Sub procedure.

After that, we Run the code.

Therefore, you will see an InputBox will prompt up.

Then, we select range B8:D9 in the InputBox >> click OK.

Therefore, you can see the range B8:D9 in cell C5.

Result of InputBox with cell reference

Read More: VBA InputBox for Integer Value Only in Excel


6. InputBox with an Array (Type 64)

In Application.InputBox, Type 64 is used to input an array of value. Here, in the following video you can see that we have 5 student names in cells B5:B9. Next, to find out the ID No. of these students, we used an array. Moreover, we input the array in the InputBox. Finally, we can the result in cells C5:C9.

First of all, we will type the following VBA code in Sheet7.

Sub type64_Array()
Dim st_array As String
input_array = Application.InputBox _
("Type an array", Type:=64)
For i = LBound(input_array) To UBound(input_array)
st_array = st_array & input_array(i) & vbLf
Next i
Range("C5:C10") = WorksheetFunction.Transpose _
(Split(st_array, vbLf))
End Sub

🔎 Code Breakdown:

Sub type64_Array()

We take type64_Array as the Sub procedure.

Dim st_array As String
We declare st_array as a String.
input_array = Application.InputBox _
("Type an array", Type:=64)

We assign Application.InputBox in the input_array variable. Also, we declare Type 64 for an array.

For i = LBound(input_array) To UBound(input_array)

For loop is used for each element of the input array to determine the lower and upper bounds of the array using the LBound and UBound functions.

st_array = st_array & input_array(i) & vbLf

The current array element is concatenated by st_array variable, then we used a line feed character (vbLf) to separate the elements.

Next i

This ends the For loop.

Range("C5:C10") = WorksheetFunction.Transpose _
(Split(st_array, vbLf))

This line uses the SPLIT function and the line feed character (vbLf) as the delimiter to split the concatenated string in st array into an array. The array that results is then transposed and written to the range C5:C10.

End Sub

Finally, we end the subprocedure.

Afterward, we Run the code.

Therefore, you will see an InputBox will prompt up.

Then, we type the array {1,2,3,4,5} in the InputBox >> click OK.

Therefore, you can see the ID No. in cells C5:C9.

Outcome of InputBox with an array


Other Different Types of VBA Inputbox in Excel

Aside from the types of InputBox described above, there are a few others. These are VBA InputBox with Options, VBA InputBox with Multiple Inputs, and VBA InputBox with Drop-Down List.  These types are described below.


1. VBA InputBox with Options

VBA InputBox can have multiple options. The user can select one of the options, and input the selected option in the InputBox. From the following video, you can understand that we want Student Name in cell B5 and Group in cell C5. Here, we want to set two groups,  A and B as two messages in the InputBox. Then, we will input one group name in the InputBox. Since the InputBox has two messages as two options, this will make the InputBox with Options.

First of all, we will type the following VBA code in a Module.

Sub inputbox_with_options()
Dim student_name As String, msg_1 As String, _
msg_2 As String, msg_3 As String, _
b_title As String, b_default As String
Dim Grade As String
msg_1 = "Enter the Group Name Here"
msg_2 = "Group A" & vbTab & _
"student who got over 80% in all subjects"
msg_3 = "Group B" & vbTab & _
"student who got below 80% in all subjects"
b_title = "XYZ School"
b_default = "Be Cautious"
student_name = InputBox _
("Enter Name of the Student", b_title, b_default)
Grade = InputBox(msg_1 & _
vbCrLf & vbCrLf & msg_2 & vbCrLf & msg_3, b_title, b_default)
Range("B5").Value = student_name
Range("C5").Value = Grade
End Sub

🔎 Code Breakdown:

Sub inputbox_with_options()

Here, we take inputbox_with-options as the Sub procedure.

Dim student_name As String, msg_1 As String, _
msg_2 As String, msg_3 As String, _
b_title As String, b_default As String
Dim Grade As String

We declare student_name, msg_1, msg_2, msg_3, b_title,b_default, and Grade as String.

msg_1 = "Enter the Group Name Here"
msg_2 = "Group A" & vbTab & _
"student who got over 80% in all subjects"
msg_3 = "Group B" & vbTab & _
"student who got below 80% in all subjects"
b_title = "XYZ School"
b_default = "Be Cautious"

We assign value to the above-declared variables.

student_name = InputBox _
("Enter Name of the Student", b_title, b_default)

An InputBox is assigned to student_name variable. The user will type a student name when the InputBox will pop up. The InputBox will have a title and a default value.

Grade = InputBox(msg_1 & _
vbCrLf & vbCrLf & msg_2 & vbCrLf & msg_3, b_title, b_default)

This InputBox is assigned to Grade variable. This InputBox has 3 messages. We seperated the messages by & operator and vbCrLf constant. The vbCrLf adds a line break between the messages.

Range("B5").Value = student_name
Range("C5").Value = Grade

These lines assign the values of “student_name” and “Grade” to cells B5 and C5, respectively.

End Sub

Finally, we end the Sub procedure.

Outcome of InputBox with Multiple Options


2. Excel VBA Multiple InputBox for Taking Multiple Inputs

We can insert multiple InputBoxes in one VBA code. These multiple InputBoxes will take multiple inputs. These inputs can be of the same or different types. Also, you can add InputBox and Application. Inputbox in the same VBA code. You can look up into Type 0 and Type 4 InputBoxes described earlier.

Here, in the following video, you can easily notice that we want Student Name in cell B5 and the State in cell C5. For this purpose, we will use two InbutBoxes. In one InputBox, we will input the Student Name, and in the next one, we will input the State name. Also, we will show the inputs in cells B5 and C5.

First of all, we will type the following VBA code in a Module.

Sub InputBox_with_Mutiple_Inputs()
Dim student_name As String
Dim state As String
student_name = InputBox _
("Enter the Student Name:", "Enter Here")
state = InputBox("Enter the state Name:")
Range("B5") = student_name
Range("C5") = state
End Sub

🔎 Code Breakdown:

Sub InputBox_with_Mutiple_Inputs()

Takes InputBox_with_Multiple_Inputs as the Sub procedure.

Dim student_name As String
Dim state As String

Declare studnet_name and state as String.

student_name = InputBox _
("Enter the Student Name:", "Enter Here")
state = InputBox("Enter the state Name:")

Takes the first InputBox for student_name and the second InputBox for the state.

Range("B5") = student_name
Range("C5") = state

These lines take Range B5 to show student_name and Range C5 to show state.

End Sub

Finally, we end the Sub procedure.

Then, you have to Run the code. After running the code, you will see two InputBoxes like the above video. Then input your value.

Read More: Vbscript InputBox with Multiple Fields in Excel


Frequently Asked Questions (FAQs)

Here, we will answer some frequently asked questions regarding type of Excel VBA InputBox.

  • How Do I Use InputBox in Excel VBA?

You can use InputBox by simply typing the InputBox or Application.InputBox function.

See the following line, you can copy this line and paste it into your code to use InputBox.

Student_name= InputBox("Enter the name here:")
  • How to Get Input from TextBox in VBA?

We normally useTextBoxes to collect input from users. They are a part of UserForms. A text box can be displayed on the UserForm so the user can enter data. It also allows users to enter data on the worksheet using simple codes.

For a better understanding, you can read the following article:

Excel VBA: Get Value From Userform Textbox

  • What is Type:=8 InputBox in VBA?

In Application. InputBox, Type 8 is used for Cell Reference. When a user defines Type 8, then the user must input a cell or a range of cells in InputBox

  • How Do I Input a Box Value in a Cell in Excel VBA?

To Input a box value in a cell in Excel VBA, you can use the Range(“Cell”) method. See the following part from a VBA code.

Student_name= InputBox("Enter the name here:")
Range("D5") = Student_name

Here, we assign an InputBox to the Student_name variable.

Then, we specified cell D5 using the Range method, so that the Student_name variable’s value is stored there.


Key Takeaways for Article

This article is useful for readers who are looking for the Type of Excel VBA InputBox. Let’s take a glance at what we learned from this article:

  • You have learned how you can create an InputBox.
  • How to create an Application.InputBox.
  • Difference Between InputBox and Application.InputBox.
  • 7 Variable Types of Applications.InputBox.
  • VBA InputBox with Options, VBA InputBox with Multiple Inputs, and VBA InputBox with Drop-Down List.

Download Practice Workbook

You can download the Excel file and practice the explained methods.


Conclusion

In this article, we extensively describe the Type of Excel VBA InputBox. We tried to make this article as informative as we can. We hope this was helpful to you. Please let us know in the Comment section if you have any queries or suggestions.

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:

Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo