Here is an image overview of using a constant (vbNewLine or vbCr) of the InputBox function that allows multiple lines in the InputBox. This InputBox, with a brief instruction, asks the user to enter a customer name in the specified box. Here’s what you need to know.
Overview of an InputBox in Excel VBA
Syntax:
InputBox(prompt, [title], [default], [xpos], [ypos], [helpfile, context])
| Syntax Element | Description |
|---|---|
| Prompt (Required) | Message in the dialog box. It is the only required argument for the function. The Prompt argument can be up to 1024 characters long and can include message text, instructions, or questions for the user to answer. |
| Title (Optional) | Title of the dialog box. If omitted, the application name is used as the title. The Title argument can be up to 255 characters long and can be used to provide additional information or context to the user. |
| Default (Optional) | The default response if no other input is entered. If omitted, the text box will be empty. The Default argument can be up to 255 characters long and can be used to suggest a default response to the user. |
| Xpos (Optional) | The XPos argument is a numeric expression that specifies the horizontal position of the dialog box relative to the screen in pixels. If omitted, the dialog box is centered horizontally. The XPos argument can be used to position the dialog box at a specific location on the screen. |
| Ypos (Optional) | Specifies the vertical position of the dialog box relative to the screen in pixels. If omitted, the dialog box is centered vertically. The YPos argument can be used to position the dialog box at a specific location on the screen. |
| Helpfile (Optional) | Identifies the Help file that can be used to provide context-sensitive Help for the dialog box. |
| Context (Optional) | Assigned to the appropriate Help topic by the Help author. |
How to Open the VBA Macro Editor in Excel
- Click on the Developer tab and select Visual Basic. Alternatively, you can press Alt + F11.
- A new window will appear. This is the Visual Basic Editor.
- To write code, go to Insert and Module.
- Once you input code, click on the Run button to run the code. You can also press the F5 key.
Excel VBA InputBox with Multiple Lines: 3 Examples
In the sample data set, we have the feedback survey of customers who dined at a restaurant. The dataset contains the name of the customers, their age, their gender, the rating score that they provided, and their feedback in a table.
Example 1 – Use InputBox Functions to Create an InputBox with Multiple Lines in Excel VBA
We need to use some constants inside the InputBox function to separate one line from another.
Case 1.1 – Use the vbNewLine Constant in the InputBox Function
- Enter the following code in your VBA Editor and press the Run button or F5 key:
Sub InputBox_vbNewLine()
'variable declaration
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim feedback As String
Title = Range("B2").Value
Set myRng = Range("B4:F14")
'InputBox with multiple lines
customer_name = InputBox(Title & vbNewLine & _
"The feedback of customers who have dined at this restaurant" & _
vbNewLine & "Please insert the name of the customer below :")
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
feedback = myRng.Cells(i, myRng.Columns.Count)
MsgBox "Feedback from " & customer_name & vbNewLine & _
feedback
Exit For
End If
Next i
End SubVBA Breakdown
Sub InputBox_vbNewLine()
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim feedback As String
Title = Range("B2").Value
Set myRng = Range("B4:F14")- This code defines a subroutine called InputBox_vbNewLine. It declares four variables – customer_name, Title, myRng, and feedback.
customer_name = InputBox(Title & vbNewLine & _
"The feedback of customers who have dined at this restaurant" & _
vbNewLine & "Please insert the name of the customer below :")
It prompts the user to enter a customer name using the InputBox function.
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
feedback = myRng.Cells(i, myRng.Columns.Count)
MsgBox "Feedback from " & customer_name & vbNewLine & feedback
Exit For
End If
Next i
End Sub- It searches for the name in the range B4:F14 set as myRng. If the name is found, it displays the feedback for that customer using a MsgBox.
- Put the name of any customer in the specified box of the multiline InputBox.
- Press OK and you will see the feedback of the customer.
Read More: How to Create Yes-No InputBox with Excel VBA
Case 1.2 – Use the vbCr Constant in the InputBox Function
- Enter the following code in your VBA Editor and press the Run button or F5 key:
Sub InputBox_vbCr()
'variable declaration
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim score As Integer
Title = Range("B2").Value
Set myRng = Range("B4:F14")
'inputBox with multiple lines
customer_name = InputBox(Title & vbCr & _
"The feedback of customers who have dined at this restaurant" & _
vbCr & "Please insert the name of the customer below :")
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
score = myRng.Cells(i, myRng.Columns.Count - 1)
MsgBox "Score given by " & customer_name & vbCr & score
Exit For
End If
Next i
End SubVBA Breakdown
Sub InputBox_vbCr()
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim score As Integer
Title = Range("B2").Value
Set myRng = Range("B4:F14")- This code defines a subroutine called InputBox_vbCr. It declares four variables – customer_name, Title, myRng, and score.
customer_name = InputBox(Title & vbCr & _
"The feedback of customers who have dined at this restaurant" & _
vbCr & "Please insert the name of the customer below:")- It prompts the user to enter a customer name using the InputBox function.
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
score = myRng.Cells(i, myRng.Columns.Count - 1)
MsgBox "Score given by " & customer_name & vbCr & score
Exit For
End If
Next i
End Sub- It searches for the name in the range B4:F14. If the name is found, it displays the score given by that customer using a MsgBox.
- Put the name of a customer in the specified box of the multiline InputBox.
- Press OK and you will see the score given by the customer.
Read More: Excel VBA: Create InputBox with Multiple Inputs
Case 1.3 – Use the vbLf Constant in the InputBox Function
- Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
Sub InputBox_vbLf()
'variable declaration
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim age As String
Title = Range("B2").Value
Set myRng = Range("B4:F14")
'InputBox with multiple lines
customer_name = InputBox(Title & vbLf & _
"The feedback of customers who have dined at this restaurant" & _
vbLf & "Please insert the name of the customer below :")
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
age = myRng.Cells(i, 2)
MsgBox customer_name & " is " & age & " years old"
Exit For
End If
Next i
End SubVBA Breakdown
Sub InputBox_vbLf()
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim age As String
Title = Range("B2").Value
Set myRng = Range("B4:F14")- This code defines a subroutine called InputBox_vbLf. It declares four variables – customer_name, Title, myRng, and age.
customer_name = InputBox(Title & vbLf & _
"The feedback of customers who have dined at this restaurant" & _
vbLf & "Please insert the name of the customer below :")- It prompts the user to enter a customer name using the InputBox function.
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
age = myRng.Cells(i, 2)
MsgBox customer_name & " is " & age & " years old"
Exit For
End If
Next i
End Sub- It searches for the name in the range B4:F14. If the name is found, it displays the age of that customer using a MsgBox.
- Put a name in the specified box of the multiline InputBox.
- Press OK and you will see the age of the customer.
Read More: How to Use Excel VBA InputBox with Example
Case 1.4 – Use the vbCrLf Constant in the InputBox Function
- Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
Sub InputBox_vbCrLf()
'variable declaration
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim rating As Integer
Title = Range("B2").Value
Set myRng = Range("B4:F14")
'InputBox with multiple lines
customer_name = InputBox(Title & vbCrLf & _
"The feedback of customers who have dined at this restaurant" & _
vbCrLf & "Please insert the name of the customer below :"
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
rating = myRng.Cells(i, 4)
If rating <= 5 Then
MsgBox customer_name & " has given poor ratings"
Else
MsgBox customer_name & " has given good ratings"
End If
Exit For
End If
Next i
End SubVBA Breakdown
Sub InputBox_vbCrLf()
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim rating As Integer
Title = Range("B2").Value
Set myRng = Range("B4:F14")- This code defines a subroutine called InputBox_vbCrLf. It declares four variables – customer_name, Title, myRng, and rating.
customer_name = InputBox(Title & vbCrLf & _
"The feedback of customers who have dined at this restaurant" & _
vbCrLf & "Please insert the name of the customer below :")- It prompts the user to enter a customer name using the InputBox function.
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
rating = myRng.Cells(i, 4)
If rating <= 5 Then
MsgBox customer_name & " has given poor ratings"
Else
MsgBox customer_name & " has given good ratings"
End If
Exit For
End If
Next i
End Sub- It then searches for the name in the range B4:F14 which is set as myRng. If the name is found, it displays whether the customer has given poor or good ratings based on the rating obtained from the fourth column of the range B4:F14.
- Put a name in the specified box of the multiline InputBox.
- Press OK and you will see how good or poor the rating of the customer was.
Example 2 – Use the Application.InputBox Method to Create an InputBox with Multiple Lines in Excel VBA
We can use the Application.InputBox method to create an InputBox with multiple lines in Excel VBA. The Application.InputBox method prompts the user to select data from the sheet rather than writing in the InputBox.
Why Application.InputBox?
Both InputBox and Application.InputBox is used to prompt the user for input. However, the Application.InputBox method allows the user to select a range of cells that may contain any type of data (number, text, formula, etc.) without specifying the exact data type. So, the Application.InputBox method provides more flexibility and control over the user input process. Since the user selects data from the dataset instead of typing it into the InputBox, this is a more reliable method and reduces errors.
Case 2.1 – Use the vbNewLine Constant in the Application.InputBox Method
- Enter the following code in your VBA Editor and press the Run button or F5 key:
Sub Application_InputBox_vbNewLine()
'variable declaration
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim feedback As String
Title = Range("B2").Value
Set myRng = Range("B4:F14")
'InputBox with multiple lines
customer_name = Application.InputBox(Title & vbNewLine & _
"The feedback of customers who have dined at this restaurant" & _
vbNewLine & _
"Please select the name of the customer from the table :")
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
feedback = myRng.Cells(i, myRng.Columns.Count)
MsgBox "Feedback from " & customer_name & vbNewLine & feedback
Exit For
End If
Next i
End SubVBA Breakdown
Sub Application_InputBox_vbNewLine()
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim feedback As String
Title = Range("B2").Value
Set myRng = Range("B4:F14")- This code defines a subroutine called Application_InputBox_vbNewLine. It declares four variables – customer_name, Title, myRng, and feedback.
customer_name = Application.InputBox(Title & vbNewLine & _
"The feedback of customers who have dined at this restaurant" & _
vbNewLine & "Please select the name of the customer from the table :")- It prompts the user to select a customer name from a table using the Application.InputBox method.
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
feedback = myRng.Cells(i, myRng.Columns.Count)
MsgBox "Feedback from " & customer_name & vbNewLine & feedback
Exit For
End If
Next i
End Sub- It searches for the name in the range B4:F14. If the name is found, it displays the feedback from the customer.
- Select the name of the customer from the worksheet in the specified box of the multiline InputBox.
- Press OK and you will see the feedback of the customer.
Case 2.2 – Use the vbCr Constant in the Application.InputBox Method
- Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
Sub Application_InputBox_vbCr()
'variable declaration
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim score As Integer
Title = Range("B2").Value
Set myRng = Range("B4:F14")
'InputBox with multiple lines
customer_name = Application.InputBox(Title & vbCr & _
"The feedback of customers who have dined at this restaurant" & _
vbCr & "Please select the name of the customer from the table :")
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
score = myRng.Cells(i, myRng.Columns.Count - 1)
MsgBox "Score given by " & customer_name & vbCr & score
Exit For
End If
Next i
End SubVBA Breakdown
Sub Application_InputBox_vbCr()
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim score As Integer
Title = Range("B2").Value
Set myRng = Range("B4:F14")- This code defines a subroutine called Application_InputBox_vbCr. It declares four variables – customer_name, Title, myRng, and score.
customer_name = Application.InputBox(Title & vbCr & _
"The feedback of customers who have dined at this restaurant" & _
vbCr & "Please select the name of the customer from the table :")- It prompts the user to select a customer name from a table using the Application.InputBox method.
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
score = myRng.Cells(i, myRng.Columns.Count - 1)
MsgBox "Score given by " & customer_name & vbCr & score
Exit For
End If
Next i
End Sub- It then searches for the name in the range B4:F14. If the name is found, it displays the score given by that customer.
- Select the name of the customer from the worksheet in the specified box of the multiline InputBox.
- Press OK and you will see the score given by the customer.
Case 2.3 – Use the vbLf Constant in the Application.InputBox Method
- Enter the following code in your VBA Editor and press the Run button or F5 key:
Sub Application_InputBox_vbLf()
'variable declaration
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim age As String
Title = Range("B2").Value
Set myRng = Range("B4:F14")
'InputBox with multiple lines
customer_name = Application.InputBox(Title & vbLf & _
"The feedback of customers who have dined at this restaurant" & _
vbLf & _
"Please select the name of the customer from the table :")
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
age = myRng.Cells(i, 2)
MsgBox customer_name & " is " & age & " years old"
Exit For
End If
Next i
End SubVBA Breakdown
Sub Application_InputBox_vbLf()
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim age As String
Title = Range("B2").Value
Set myRng = Range("B4:F14")- This code defines a subroutine called Application_InputBox_vbLf. It declares four variables – customer_name, Title, myRng, and age.
customer_name = Application.InputBox(Title & vbLf & _
"The feedback of customers who have dined at this restaurant" & _
vbLf & "Please select the name of the customer from the table :")- It prompts the user to select a customer name from a table using the Application.InputBox method.
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
age = myRng.Cells(i, 2)
MsgBox customer_name & " is " & age & " years old"
Exit For
End If
Next i
End Sub- It searches for the name in the range B4:F14. If the name is found, it displays the age of that customer.
- Select the name of the customer from the worksheet in the specified box of the multiline InputBox.
- Press OK and you will see the age of the customer.
Case 2.4 – Use the vbCrLf Constant in the Application.InputBox Method
- Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
Sub Application_InputBox_vbCrLf()
'variable declaration
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim rating As Integer
Title = Range("B2").Value
Set myRng = Range("B4:F14")
'InputBox with multiple lines
customer_name = Application.InputBox(Title & vbCrLf & _
"The feedback of customers who have dined at this restaurant" & _
vbCrLf & _
"Please select the name of the customer from the table :")
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
rating = myRng.Cells(i, 4)
If rating <= 5 Then
MsgBox customer_name & " has given poor ratings"
Else
MsgBox customer_name & " has given good ratings"
End If
Exit For
End If
Next i
End SubVBA Breakdown
Sub Application_InputBox_vbCrLf()
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim rating As Integer
Title = Range("B2").Value
Set myRng = Range("B4:F14")- This VBA code is a subroutine and declares some variables of string, integer, and range type.
customer_name = Application.InputBox(Title & vbCrLf & _
"The feedback of customers who have dined at this restaurant" & _
vbCrLf & "Please select the name of the customer from the table :")- It displays an InputBox with multiple lines to prompt the user to select a customer’s name from a table.
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
rating = myRng.Cells(i, 4)
If rating <= 5 Then
MsgBox customer_name & " has given poor ratings"
Else
MsgBox customer_name & " has given good ratings"
End If
Exit For
End If
Next i
End Sub- It then looks for the selected customer’s name in a range and retrieves the rating given by the customer. If the rating is less than or equal to 5, a MsgBox will display that the customer has given poor ratings; otherwise, the MsgBox will display that the customer has given good ratings. The code uses the vbCrLf constant to create line breaks in the InputBox
- Select the name of the customer from the worksheet in the specified box of the multiline InputBox.
- Press OK and you will see how good or poor the rating of the customer was.
Example 3 – Create a Userform to Show an InputBox with Multiple Lines in Excel VBA
Here’s the overview for a UserForm code.
- Go to Insert and select UserForm.
- From the ToolBox, you can take a Label to write anything. This is where you can write multiple lines.
- Make a TextBox to collect user input just like the InputBox.
- Use a CommandButton that works just like an InputBox
- You can change the properties of these tools from the Properties Window (press F4 to view the window).
- See the video below to understand the whole procedure in detail.
- The Label along with the TextBox represent an InputBox. The Label shows instructions with multiple lines and the TextBox takes input. The CommandButton is added to run a VBA code.
- Click on the CommandButton twice and a new window will appear.
- Put the following code in the window:
Private Sub CommandButton1_Click()
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim feedback As String
Title = Range("B2").Value
Set myRng = Range("B4:F14"
customer_name = TextBox1.Value
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
feedback = myRng.Cells(i, myRng.Columns.Count)
MsgBox feedback
Exit For
End If
Next i
End Sub- Run the UserForm to see the results.
VBA Breakdown
Private Sub CommandButton1_Click()
Dim customer_name As String
Dim Title As String
Dim myRng As Range
Dim feedback As String
Title = Range("B2").Value
Set myRng = Range("B4:F14")- This VBA code defines a subroutine that runs when CommandButton1 is clicked. It declares and initializes four variables, including customer_name, Title, myRng, and feedback.
Set myRng = Range("B4:F14")
customer_name = TextBox1.Value
For i = 1 To myRng.Rows.Count
If myRng.Cells(i, 1) = customer_name Then
feedback = myRng.Cells(i, myRng.Columns.Count)
MsgBox feedback
Exit For
End If
Next i
End Sub- The code retrieves the value from a text box named TextBox1, searches a range of cells for a matching value, and displays the corresponding value from the last column of the range in a MsgBox.
Read More:How to Use VBA Input Box with Buttons in Excel
How to Create InputBox with Multiple Lines That Works with a Specific Data Type in Excel VBA
We can create an InputBox with multiple lines that work with specific data. You can store the input value in a variable by using the InputBox function and check if the variable is of a particular data type. The data type can be defined by using the Type parameter in the Application.InputBox method.
| Type Parameter Value | Input |
|---|---|
| 0 | Formula |
| 1 | Number |
| 2 | Text |
| 4 | Boolean |
| 8 | Range object |
| 16 | Error |
| 64 | Array of values |
Method 1 – Using the InputBox Function with a Specific Data Type in Excel VBA
- Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
Sub InputBox_SpecificData()
'variable declaration
Dim customer_name As String
customer_name = InputBox("Please insert a valid customer name" & _
vbNewLine & "from the survey table")
'check if the input is numeric
If IsNumeric(customer_name) Then
MsgBox "You did not insert a valid name!", vbInformation
End If
End SubVBA Breakdown
Sub InputBox_SpecificData()
Dim customer_name As String- This VBA code defines a subroutine named InputBox_SpecificData. It first declares a variable called customer_name as a string data type.
customer_name = InputBox("Please insert a valid customer name" & vbNewLine & "from the survey table")- It prompts the user with an InputBox to enter a valid customer name from the survey table.
If IsNumeric(customer_name) Then
MsgBox "You did not insert a valid name!", vbInformation
End If
End Sub- The code checks if the entered customer name is numeric and displays a Msgbox with an error message if it is.
- Insert a valid customer name in the specified box of the multiline InputBox.
- If you put a number or a special character, you will get an error message. This InputBox accepts only text data.
Method 2 – Using the Application.InputBox Method with a Specific Data Type in Excel VBA
- Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
Sub Application_InputBox_SpecificData()
'variable declaration
Dim customer_score As Integer
Dim myRng As Range
Set myRng = Range("B4:F14")
'InputBox with specific data type
customer_score = Application.InputBox _
("The ratings of customers who have dined at this restaurant" & _
vbNewLine & "Please select the score from the table :", Type:=1)
If customer_score > 5 Then
MsgBox "It is a good rating"
Else
MsgBox "It is a poor rating"
End If
End SubVBA Breakdown
Sub Application_InputBox_SpecificData()
Dim customer_score As Integer
Dim myRng As Range
Set myRng = Range("B4:F14")- This VBA Excel code defines a subroutine named Application_InputBox_SpecificData. It declares two variables, customer_score as an integer data type and myRng as a range data type. It sets the range of cells B4:F14 to myRng.
customer_score = Application.InputBox("The ratings of customers who have dined at this restaurant" & vbNewLine & "Please select the score from the table :", Type:=1)
If customer_score > 5 Then
MsgBox "It is a good rating"
Else
MsgBox "It is a poor rating"
End If
End Sub
- It prompts the user with an InputBox to select a customer score that must be a number (Type:=1) from the table. If the customer chooses a different data type, it will show an error message. If the customer chooses a number, it displays a MsgBox with a corresponding rating based on the entered score.
How to Insert Multiple Lines in MsgBox with Excel VBA
We can create a MsgBox with multiple lines using the same constants described in Method 1.
- Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
Sub MsgBox_MultipleLines()
'variable declaration
Dim name As Range
Dim customer_name As String
Dim age As Integer
Dim gender As String
Set name = Application.InputBox _
("Please select the name of the customer", Type:=8)
customer_name = name.Cells(1, 1)
age = name.Cells(1, 2)
gender = name.Cells(1, 3)
'MsgBox with multiple lines
MsgBox "Information of the customer:" & _
vbCr & "Name = " & customer_name & vbCr & _
"Age = " & age & vbCr & "Gender = " & gender
End SubVBA Breakdown
Sub MsgBox_MultipleLines()
Dim name As Range
Dim customer_name As String
Dim age As Integer
Dim gender As String- This VBA code defines a subroutine named MsgBox_MultipleLines. It declares four variables, name as a range data type, customer_name as a string data type, age as an integer data type, and gender as a string data type.
Set name = Application.InputBox("Please select the name of the customer", Type:=8)
customer_name = name.Cells(1, 1)
age = name.Cells(1, 2)
gender = name.Cells(1, 3)
MsgBox "Information of the customer:" & vbCr & _
"Name = " & customer_name & vbCr & _
"Age = " & age & vbCr & "Gender = " & gender
End Sub- It prompts the user to select a name from a range of cells and assigns the corresponding values of age and gender to the declared variables. Finally, it displays a MsgBox with the customer’s information using the MsgBox function with multiple lines.
- Select a customer name from the worksheet in the specified box of the InputBox.
- Press OK and you will see information of that customer in a MsgBox with multiple lines.
How to Take Multiple Inputs Through Multiple InputBox in Excel VBA
Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
Sub InputBox_MultipleInputs()
'variable declaration
Dim myRng, info As Range
Set myRng = Range("B15:F15")
Set info = Range("B4:F4")
'put multiple inputs in different cells
For i = 1 To 5
myRng.Cells(i) = InputBox _
("Please insert the following information :" & _
vbLf & info(i))
Next i
End SubVBA Breakdown
Sub InputBox_MultipleInputs()
Dim myRng, info As Range
Set myRng = Range("B15:F15")
Set info = Range("B4:F4")- This Excel VBA code defines a subroutine named InputBox_MultipleInputs. It declares two variables, myRng as a range data type and info as a range data type. It sets the range of cells B15:F15 to myRng and the range of cells B4:F4 to info.
For i = 1 To 5
myRng.Cells(i) = InputBox("Please insert the following information :" & vbLf & info(i))
Next i
End Sub- Finally, it prompts the user to enter multiple inputs for each cell in myRng based on the information provided in the corresponding cells of info using a For Loop.
Things to Remember
- Use the concatenation operator & to join multiple lines of text or variables.
- Use the correct constant to add a line break between each line of text.
- You should give proper directions through the message of the InputBox on what type of data is allowed to insert.
Frequently Asked Questions
How can I add line breaks in an InputBox in Excel VBA?
Use the appropriate constant to put line breaks in an InputBox. Please go through Method 1 and Method 2 to understand the procedure.
How can I specify the type of input in an InputBox in Excel VBA?
Use the Type parameter of the InputBox function to specify the type of input you want. For example, InputBox(“Enter a number:”, Type:=1) will only accept numeric inputs.
How can I store the user input in a variable or a range of cells in Excel VBA?
Use a variable to store the user input by assigning the value returned by the InputBox function to the variable. Alternatively, you can use the Value property of a range of cells to store the user input. For example: Range(“A5”).Value = InputBox(“Enter the value”).
Download the Practice Workbook
Related Articles
- 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
