Looking for ways to create an inputbox with multiple inputs using VBA in Excel? Then, this is the right place for you.
Excel VBA InputBox is a built-in function that allows you to prompt the user for input. It’s a useful tool when you need to get user input for your further use purpose. With InputBox in Excel VBA, you can prompt the user for a single input value, such as a name or a number. However, in some cases, you might need to prompt the user for multiple input values. This is where the InputBox with multiple inputs comes in handy. To get an early view of how we’re dealing this case in this article, you may check this video:
In this article, I’ll show you effective ways of how to take multiple inputs from users using VBA InputBox in Excel.
Is It possible to Take Multiple Inputs in Single InputBox?
You may check this video to get a glimpse of how we can achieve this using array:
Using Array Method to Take Multiple Inputs in Single InputBox
In short, it is possible to take multiple inputs in a single InputBox. Although it requires some kind of convoluted processes. In this example, you may use an Array to take multiple inputs using InputBox. In this example, we’re taking Employee ID, Name, Region and Product as input and we’ll store these data in columns ranging from B to E. Our Dataset looks like this:
Whenever we’ll write the code, we’ll start by following these steps first.
Now, we’ll rename this module as MultipleInputUsingArray to make the reference easy and write the following code in this module.
Sub Multiple_Input()
Dim userInputs As Variant
' Get user inputs using InputBox
inputFromUser = InputBox("Please enter the Employee ID, Name, Sales Region, and Product separated by comma:", "User Inputs")
' Convert to Array
userInputs = Split(inputFromUser, ",")
' Add user inputs to the first blank row
Dim lastRow As Long
lastRow = Range("B" & Rows.Count).End(xlUp).Row + 1
Range("B" & lastRow).Value = userInputs(0)
Range("C" & lastRow).Value = userInputs(1)
Range("D" & lastRow).Value = userInputs(2)
Range("E" & lastRow).Value = userInputs(3)
End SubCode Breakdown:
Here, I’ve shown the code breakdown to ease your understanding.
Sub Multiple_Input()- This is a sub procedure named Multiple Input.
Dim userInputs As Variant- A variable named userInputs is declared as a Variant data type that can hold any type of value
inputFromUser = InputBox("Please enter the Employee ID, Name, Sales Region, and Product separated by comma:", "User Inputs")- The InputBox function is used to prompt the user to enter four pieces of information separated by a comma. The title of the InputBox is User Inputs. The user’s input is assigned to a variable named inputFromUser.
userInputs = Split(inputFromUser, ",")- The Split function is used to split the inputFromUser variable into an array of four elements, with each element containing one of the four pieces of information entered by the user. The delimiter used to split is a comma.
Dim lastRow As Long
lastRow = Range("B" & Rows.Count).End(xlUp).Row + 1- A variable named lastRow is declared as a Long data type. The value of lastRow is set to the last row number in column B that contains data, plus one. This will be the row where the user’s input will be added.
Range("B" & lastRow).Value = userInputs(0)
Range("C" & lastRow).Value = userInputs(1)
Range("D" & lastRow).Value = userInputs(2)
Range("E" & lastRow).Value = userInputs(3)- The Range function is used to write the user’s input to the worksheet. The first element of the userInputs array (Employee ID) is written to column B. All other Range functions work in the same manner. The row number used for the write is the value of lastRow determined in the immediate previous explanation.
End Sub- The sub procedure ends.
Now, press F5 or click the Run button to run the code.
After running the macro, it’ll prompt InputBox where the user will input the data accordingly. When inserting the input will finish, the data will be stored in the next blank row. The final output will look like this:
Read More: How to Use Excel VBA InputBox with Example
Using Multiple InputBox to Take Multiple Inputs in Excel
We can perform the task in another way. We may simply use multiple InputBox and take the inputs from the user. Now we’ll take the inputs from the user using multiple InputBox. In the following video, you may find how we can simply use multiple InputBox to take multiple inputs from the user:
We’ll follow the exact same step as we’ve just mentioned creating a new Module named MultipleInputBox where we’ll write the code to take multiple inputs from the user.
Sub Multiple_Input()
' Get user inputs using InputBox
EmployeeID = InputBox("Please enter the Employee ID:", "Employee ID")
Name = InputBox("Please enter the Name:", "Name")
SalesRegion = InputBox("Please enter the Sales Region:", "Sales Region")
Product = InputBox("Please enter the Product Name:", "Product Name")
' Find first blank row in column B
Dim lastRow As Long
lastRow = Range("B" & Rows.Count).End(xlUp).Row + 1
' Add user inputs to the first blank row
Range("B" & lastRow).Value = EmployeeID
Range("C" & lastRow).Value = Name
Range("D" & lastRow).Value = SalesRegion
Range("E" & lastRow).Value = Product
End SubCode Breakdown:
Sub Multiple_Input()- This is a sub procedure named Multiple_Input.
EmployeeID = InputBox("Please enter the Employee ID:", "Employee ID")Name = InputBox("Please enter the Name:", "Name")
SalesRegion = InputBox("Please enter the Sales Region:", "Sales Region")
Product = InputBox("Please enter the Product Name:", "Product Name")- The InputBox function is used to prompt the user to enter four pieces of information. Four separate InputBox windows are displayed, each with its own prompt message and window title. The values entered by the user are stored in four separate variables named Employee ID, Name, SalesRegion and Product.
Dim lastRow As Long
lastRow = Range("B" & Rows.Count).End(xlUp).Row + 1: This statement does the exact same thing described in the previous code breakdown section.
Range("B" & lastRow).Value = EmployeeID
Range("C" & lastRow).Value = Name
Range("D" & lastRow).Value = SalesRegion
Range("E" & lastRow).Value = Product- This portion is exactly the same as the previous code.
End Sub- The sub-procedure ends.
Now press F5 or click the Run Button to run the macro.
As a result, we’ll get a prompt to input the Employee ID, Name, Region, and Product. If we put the inputs correctly, our output will look like this:
Read More: How to Use VBA Input Box with Buttons in Excel
How to Launch VBA Editor in Excel
As we’ll use the VBA environment throughout this work, we need to enable the VBA setup first if not done so. First of all, open the Developer tab. If you don’t have the developer tab, then you have to enable the developer tab. Then select the Visual Basic command.
Hence, the Visual Basic window will open. Then, we’ll insert a Module from the Insert option where we’ll write the VBA Code.
User Form: Best Way to Take Multiple Inputs from The User
First of all to get an overview of what this task looks like, you may watch this video:
If we need to take multiple inputs from the user, UserForm serves it in the best possible way. For example, if we’re taking Employee ID, Name, Region, and Product as input, we may create an UserForm with these fields. And using this userform we’ll take the input and store that input in a predefined range of a worksheet. In this case, we’ll store the data in columns ranging from B to E.
Create InputBox with Multiple Inputs With VBA UserForm in Excel: Step-by-Step Procedure
As we already know, an UserForm is the best option to get multiple inputs from the user, I’ll focus on this topic now. In the following segments, I’ll show how to create an InputBox with Multiple Inputs with VBA UserForm in Excel. So to create a UserForm of your own, you may follow my step-by-step description.
Step 1: Insert User Form to Create InputBox with Multiple Inputs in Excel
Hence, the Visual Basic window will open. Then, we’ll insert an UserForm from the Insert option where we’ll create the UserForm.
We’ll insert different controls in the UserForm according to our needs.
Our UserForm will now look like this:
Read More: Excel VBA: InputBox Type with Examples
Step 2: Create Fields in VBA UserForm
Now we’ll add Controls in the UserForm. We’ll add a total of four sets of Label and TextBox in our UserForm. First of all, we’ll add a Label which is Label1, and TextBox and edit the caption of Label1 as Employee ID. We’ll make the font bold for design purposes. You may watch the following video to see how we’ve done this:
After that, we’ll create a TextBox which is TextBox1 to take the input from the user. We’ll align Tops the TextBox1 with Label1. Remember to first select Label1 and then press and hold the Shift key while selecting TextBox1 for aligning. Watch the following video to view the process.
Our UserForm will now look like this:
Read More: Excel VBA InputBox with Multiple Lines
Step 3: Creating Other Sets of Label and TextBox by Copy Paste Method to Create InputBox with Multiple Inputs
Now that we have Label1 and TextBox1 ready, we’ll make another three sets of these setups by copying and pasting them. We’ll also make the alignment of all the Labels to Lefts and then all the TextBoxes to Lefts as well. You may watch the following video to see how to do that.
Step 4: Editing Captions of Labels
Now, we’ll set the captions of the Labels accordingly. We’ll set the captions of Label2, Label3, and Label4 as Name, Region, and Product respectively. To learn how to accomplish that, you can watch the video below.
Step 5: Adding CommandButton in VBA UserForm
Now we’ll add three CommandButton to our UserForm from the Toolbox. Watch the following video to see how we’ll add them.
After that, edit the Captions of the CommandButtons from the Properties window. Set the captions of CommandButton1, CommandButton2, and CommandButton3 as Submit, Reset, and Abort. Watch the following video to see how we’ve done this.
Finally, we need to adjust the position of the Controls and shape of the UserForm accordingly. We’ve shown it in the following video.
As a result our UserForm will look like this:
Step 6: Insert Macros for Individual Components of VBA UserForm to Create InputBox with Multiple Inputs
| Command Button | Sub-procedure |
|---|---|
| Submit | CommandButton1 |
| Reset | CommandButton2 |
| Abort | CommandButton3 |
For Textboxes:
| TextBox | Sub-procedure |
|---|---|
| Employee ID | TextBox1 |
| Name | TextBox2 |
| Region | TextBox3 |
| Product | TextBox4 |
Now, we’ll add code to the Controls. We’ll add codes to all the TextBoxes that are TextBox1, TextBox2, TextBox3, and TextBox4 as well as to all the CommandButtons that are CommandButton1, CommandButton2, and CommandButton3. First of all, we’ll insert code to CommandButton1. So, when we click Submit(CommandButton1), the data in all the TextBoxes are transferred into the next available row in the worksheet and the focus is set to TextBox1. If any of the TextBox is empty, an error message will show up saying “Please input all the data before submitting”.
For Submit Button (CommandButton1):
For this, Right Click on CommandButton1. So, the code window will open. Watch the following video to see how it looks.
Now, it’ll look like this:
Now type the following code:
Private Sub CommandButton1_Click()
Dim lastRow As Long
If TextBox1.Value = "" Or TextBox2.Value = "" Or TextBox3.Value = "" Or TextBox4.Value = "" Then
' If any textbox is empty, show a message box
MsgBox "Please input all the data before submitting", vbExclamation, "Error Message"
Else
' If all textboxes are filled out, proceed with submitting the data
lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 2).End(xlUp).Row
'Copy the format of the first row to the next row
Range("B5:E5").Copy Range("B" & lastRow + 1 & ":E" & lastRow + 1).Cells(1)
ActiveSheet.Cells(lastRow + 1, 2).Value = TextBox1.Value
ActiveSheet.Cells(lastRow + 1, 3).Value = TextBox2.Value
ActiveSheet.Cells(lastRow + 1, 4).Value = TextBox3.Value
ActiveSheet.Cells(lastRow + 1, 5).Value = TextBox4.Value
Me.TextBox1.SetFocus
End If
End SubCode Breakdown:
Private Sub CommandButton1_Click()- This is a private procedure named CommandButtion1_Click.
Dim lastRow As Long- Declares a variable named lastRow as a Long data type.
If TextBox1.Value = "" Or TextBox2.Value = "" Or TextBox3.Value = "" Or TextBox4.Value = "" Then
MsgBox "Please input all the data before submitting", vbExclamation, "Error Message"- Uses san If statement to check if any of the four TextBox controls (TextBox1, TextBox2, TextBox3 and TextBox4) are empty. If it found any of the TextBox controls empty, it will display a message box with an error message asking the user to input all the data.
Else- If all the TextBox controls are filled out, the Else block is executed.
lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 2).End(xlUp).Row- Sets the value of the lastRow to the last row number in column B that contains data.
Range("B5:E5").Copy Range("B" & lastRow + 1 & ":E" & lastRow + 1).Cells(1)- Copy the format of the first row (cells B5 to E5) to the next blank row using the Copy method of the Range object. Then, determine the next blank row by adding 1 to the value of lastRow.
ActiveSheet.Cells(lastRow + 1, 2).Value = TextBox1.Value
ActiveSheet.Cells(lastRow + 1, 3).Value = TextBox2.Value
ActiveSheet.Cells(lastRow + 1, 4).Value = TextBox3.Value
ActiveSheet.Cells(lastRow + 1, 5).Value = TextBox4.Value- Write the values of the four TextBox controls to the worksheet using the Value property of the Cells object. The row number used for the write is the value of lastRow plus 1.
Me.TextBox1.SetFocus- Uses the SetFocus method to move the focus back to TextBox1, ready for the user to input the next set of data.
End If- If statement ends.
For Reset Button (CommandButton2)- Similarly, add the following code against CommandButton2 which is Reset. When we’ll click on this button, the TextBoxes will be reset and we’re ready to take another set of inputs.
Private Sub CommandButton2_Click()
Dim ctrl As Control
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.TextBox Then
ctrl.Value = ""
End If
Next ctrl
Me.TextBox1.SetFocus
End SubCode Breakdown:
Private Sub CommandButton2_Click()- This is a private procedure named CommandButton2_Click. This code is triggered when the user clicks on CommandButton2 that is the Reset button.
Dim ctrl As Control- A variable named ctrl represents a control on the form.
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.TextBox Then
ctrl.Value = ""
End If
Next ctrl- This set of statements loops through each control on the form using a For Each loop and checks if the control is a TextBox using the TypeOf operator. If the control is a TextBox, set its Value property to an empty string to clear its contents.
Me.TextBox1.SetFocus- This statement sets the focus to TextBox1.
End Sub- The sub procedure ends.
Now we want that when we click on the Reset that is CommandButton3, it’ll unload the UserForm. To do so, we will add the code in a similar fashion.
Private Sub CommandButton3_Click()
Unload Me
End SubCode Breakdown:
This set of code Unloads the UserForm when we click the CommandButton3 which is the Abort button.
Now we’ll code against Textbox1 to set the focus to TextBox2 when the user will press the ENTER key. Watch the following video to see how we’ve done this.
The image of the code looks like this:
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
Me.TextBox2.SetFocus
End If
End SubCode Breakdown:
This set of code checks if user has pressed the ENTER key. If so, then it sets focus to TextBox2.
Similarly, we’ll do it for all the remaining TextBoxes. The code for TextBox2 looks like this:
Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
Me.TextBox3.SetFocus
End If
End SubCode Breakdown:
Similarly, this set of code checks if user has pressed the ENTER key. If so, then it sets focus to TextBox3.
Also, the code for TextBox3 will look like this:
Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
Me.TextBox4.SetFocus
End If
End SubCode Breakdown:
Now, this set of code checks if you have pressed the ENTER key. If so, then it sets focus to TextBox4.
And finally for TextBox4, the code looks like this:
Private Sub TextBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
Me.CommandButton1.SetFocus
End If
End SubCode Breakdown:
Finally this set of code checks if user has pressed the ENTER key. If so, then it sets focus to CommandButton1 that is the Submit button.
Step 7: Creating Button to Launch The UserForm
Watch the following video to see how we’ve created a shape and assigned the UserForm to it.
Now we need to create and assign a shape in the Excel worksheet so that whenever we click on that shape, our UserForm launches. To do so, from the worksheet in Excel goto Insert > Illustrations > Shapes > Rectangle: Rounded Corners. I’ve shown the steps in the video. Then type “Input Data” so that, the user may guess the use of the shape. Now press ENTER, it will set our shape now.
Now Right click on the shape and select Assign Macro from the options. This will open a list of macros and also select the macro associated with this shape. Goto New option and type the following code to launch the UserForm1 whenever we click this shape.
Sub RectangleRoundedCorners7_Click()
UserForm1.Show
End SubCode Breakdown:
When the shape is clicked, the UserForm is launched.
Read More:How to Use VBA Input Box with Buttons in Excel
Step 8: Testing Our UserForm to Insert Multiple Inputs in VBA InputBox
The video shows how our UserForm works.
Now let’s test our UserForm. Click on the shape in the Excel worksheet and our UserForm will launch. Then input the data accordingly. And we’ll get multiple inputs in Excel VBA InputBox.
How to Take Only Numbers in VBA InputBox
To get a glimpse of how it takes only numbers as input in InputBox, you may watch the following video.
Sometimes the type of input is strictly specified and we need to maintain this. For example, in our case, the Employee ID must be numeric. We’ve set the code accordingly so that it takes only numeric characters as input against Employee ID and shows error messages otherwise.
Sub Multiple_Input()
Dim user As String
Dim pass As String
' Get user inputs using InputBox
EmployeeID = InputBox("Please enter the Employee ID:", "Employee ID")
Name = InputBox("Please enter the Name:", "Name")
SalesRegion = InputBox("Please enter the Sales Region:", "Sales Region")
Product = InputBox("Please enter the Product Name:", "Product Name")
' Numeric Check
If Not IsNumeric(EmployeeID) Then
MsgBox "EmployeeID must be a number. Please try again."
Exit Sub
End If
' Find first blank row in column B
Dim lastRow As Long
lastRow = Range("B" & Rows.Count).End(xlUp).Row + 1
' Add user inputs to the first blank row
Range("B" & lastRow).Value = EmployeeID
Range("C" & lastRow).Value = Name
Range("D" & lastRow).Value = SalesRegion
Range("E" & lastRow).Value = Product
End SubCode Breakdown:
Major portion of the code is already explained. So, I’m now explaining only the unique portion:
If Not IsNumeric(EmployeeID) Then
MsgBox "EmployeeID must be a number. Please try again."
Exit Sub
End If- Here, IsNumeric function is used to check whether the value of EmployeeID is a number. If the value is not numeric, the code shows the text in the MsgBox and exits the current subroutine. So, the code will not execute any further and the user will need to enter a valid numeric value for Employee ID before the code can proceed.
Watch the following video to see how it works.
Frequently Asked Questions
1. What is the difference between InputBox and Application.InputBox in Excel VBA?
InputBox is a basic VBA function that displays a simple input prompt and returns the entered value as a string. Application.InputBox provides more control over the InputBox window, allowing for customization of the window title, message prompt, default value, input type, and other options. It also provides the ability to return input values in a variety of data types. In summary, while InputBox is simple and suitable for basic input prompts, Application.InputBox provides greater flexibility and control over the input window and value returned.
2. What is the maximum length of InputBox in Excel VBA?
The maximum length of InputBox in Excel VBA is approximately 1024 characters. This means that any message or prompt displayed in the InputBox cannot exceed this limit.
Things to Remember
When working with InputBox in Excel VBA, you should remember a couple of things like these:
- Always provide a prompt that clearly indicates what type of input you are expecting from the user.
- Press and Hold the Shift key while selecting multiple Controls simultaneously in UserForm.
- Validate the user’s input to ensure that it meets the required format.
By following these best practices, you may use the InputBox function effectively in your Excel VBA code to prompt the users for taking multiple inputs clearly and in a user friendly manner.
Download Practice Workbook
You may download the Practice Workbook and practice yourself.
Conclusion
I’ve already discussed the use of InputBox with multiple inputs in Excel VBA. I’ve demonstrated how to achieve this and showed the best possible way that is the UserForm in detail. In fact, I can recommend you use a UserForm when you need to get multiple inputs from the user. Also, you may set the data validation option using conditional statements. I’ve tried to cover every single detail that you may need. Having said that, if you need any further things to know about this, please feel free to let us know. I’ll try to cover that as well. Till then, have a good day!
Related Articles
- How to Create Yes-No InputBox with Excel VBA
- Excel VBA Input Box with Drop Down List
- Excel VBA: InputBox with Password Mask
- Excel VBA: Input Box with Options
- How to Handle VBA InputBox Cancel Button in Excel
