Excel VBA: InputBox Type with Examples

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.

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.

Syntax

The syntax of this function is as follows:

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

Parameters or Arguments

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.

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.

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.

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.

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 X. Type 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.

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 A. Type 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.

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 A. Type 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.

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_Number. Type 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.

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.

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.

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.

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.

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.

InputBox Type with Examples.xlsm

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.

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Keith Baskett Mar 4, 2024 at 12:06 AM

Hello Afia. Thank you for your tutoring however even having followed it my InputBox is not performing as expected. Essentially, I am asking the User to input the number of rows they wish to fill (with numbers) before moving onto the next column and starting the process of populating the cells again with numbers. I have set a default value, 20 as it happens but it could be any number, but the User can delete this value and leave the input box empty (null value) and click on OK or they can click the Cancel button (which comes with the InputBox whether you want it or not) and likewise the ‘X’ to close the InputBox. However, whenever those choices are made my project treats the input as an error condition and crashes the program. I have tried defining the InputBox as an Application type with both Type: = 1 and Type: = 2 being tried, changing the variable (the InputBox name) to numeric and string respectively but that makes no difference. When the User blanks the default value and does not replace it with another number the value of the variable comes up a “” but that and vbNull or Empty does not trap the fault condition. Have you any suggestion as to where I might be going wrong and how I might correct it. Hoping you can make sense of my ramblings and thank you in advance if you do give my problem your time. Kind regards Keith
- Reply
  Lutfor Rahman Shimanto Mar 10, 2024 at 10:24 AM
  Dear KEITH BASKETT,
  
  Thanks for sharing your problem with us. I understand that you are using InputBox to take the number of rows you wish to fill in a column and then repeat the process for the next columns. However, you are facing errors when you enter a text or invalid input or press the OK, Cancel, or X button without entering any value.
  
  To fix these errors, you have to include a few error-handling situations in your code. Here, I have provided a sample code that contains error handling for your described situations:
```
Sub HandleErrorsWhileFillingRows()
    Dim response As Variant
    Dim runAgain As Boolean
    Dim repeatCol As Variant
    Dim numRows As Variant
    Dim curCol As Integer
   
    runAgain = True
    curCol = 1
   
    While runAgain
        numRows = InputBox("Enter the number of rows:", "Number of Rows", 20)
        If Not numRows = "" Then
            If IsNumeric(numRows) Then
                For i = 1 To Int(numRows)
                    Cells(i, curCol).Value = i
                Next i
                repeatCol = vbNo
            Else
                MsgBox "The input was not a number"
                repeatCol = MsgBox("Do you want to repeat this column?", vbYesNo, "Repeat Column?")
            End If
        Else
            MsgBox "You entered a blank value"
            repeatCol = MsgBox("Do you want to repeat this column?", vbYesNo, "Repeat Column?")
        End If
       
        If repeatCol = vbYes Then
            runAgain = True
        Else
            response = MsgBox("Do you want to enter numbers in next column?", vbYesNo, "Enter Number in Next Column")
            If response = vbYes Then
                curCol = curCol + 1
                runAgain = True
            Else
                runAgain = False
                MsgBox "You finished filling rows"
            End If
        End If
    Wend
   
End Sub
```
  Here’s how this code operates:
  >> This code starts with the current column value of 1 (column A) and asks users to enter the number of rows they wish to fill with numbers. The value is set to 20 by default, but the users can change it to any number or text or keep it void.
  
  >> If the user input is numeric, then it enters values in cells of the current column (from 1 to the number of specified rows).
  
  >> If the user input is non-numeric, then it shows the message “The input was not a number” and asks if the user wishes to repeat filling the current column again.
  
  >> If the user clicks Yes, the previous steps are repeated for the current column. Otherwise, the code moves to the next column.
  
  >> If a user clicks OK with a void input or clicks Cancel or X buttons, then it shows the message “You entered a blank value” and asks if the user wishes to repeat filling the current column again.
  
  >> If the user clicks Yes, the previous steps are repeated for the current column. Otherwise, the code moves to the next column.
  You can preview the output in the following GIF:
  
  I hope this example will be helpful for you to understand how to handle errors while working with an InputBox in Excel VBA. Let us know your feedback.
  
  Regards,
  
  Seemanto Saha
  
  Team ExcelDemy

Excel VBA: InputBox Type with Examples

Overview of Excel VBA InputBox

Difference Between InputBox and Application.InputBox in Excel VBA

How to Create an InputBox with Excel VBA

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

1. InputBox with Formula (Type 0)

2. InputBox with Number (Type 1)

3. InputBox with Text (Type 2)

4. InputBox with a Logical Value (Type 4)

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

6. InputBox with an Array (Type 64)

Other Different Types of VBA Inputbox in Excel

1. VBA InputBox with Options

2. Excel VBA Multiple InputBox for Taking Multiple Inputs

Frequently Asked Questions (FAQs)

Key Takeaways for Article

Conclusion

Leave a reply Cancel reply