The InputBox function is a powerful tool in VBA that allows users to obtain input from users through a dialog box in Excel. It is a simple and versatile way of prompting users for information to be used in calculations, formulas, and macros. In this article, you’ll learn to get Excel VBA InputBox with multiple lines.
The InputBox function allows users to enter various data types, including numbers, text, dates, and formulas. The function can take optional arguments to customize the prompt, title, default value, and positioning of the dialog box. We can also create an InputBox with multiple lines in Excel VBA. There are different ways to create an InputBox with multiple lines. With its flexibility, it can be used to enhance the functionality of Excel by enabling automation and speeding up data entry.
Here we have used a constant (vbNewLine or vbCr etc) 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.
Overview of InputBox in Excel VBA
Syntax:
InputBox(prompt, [title], [default], [xpos], [ypos], [helpfile, context])
| Syntax Element | Description |
|---|---|
| Prompt (Required) | This argument is a string expression that appears as the 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) | The Title argument is a string expression that appears as the 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 argument is a string expression that appears in the text box as 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) | The YPos argument is a numeric expression that 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) | It identifies the Help file that can be used to provide context-sensitive Help for the dialog box. |
| Context (Optional) | It is a numeric expression. It is assigned to the appropriate Help topic by the Help author. |
How to Open VBA Macro Editor in Excel
You need to follow the steps below to get into the VBA Macro Editor in your worksheet:
- You will see the Developer tab on top of the worksheet. Click on the Developer tab and select Visual Basic. Alternatively, you can press Alt+F11.
- A new window will appear. It is the Visual Basic Editor To write a new code, go to Insert > Module.
- In the module, write the code and click on the Run button to run the code. You can also press the F5 key to run the code.
Excel VBA InputBox with Multiple Lines: 3 Examples
We can use the InputBox function in many ways. We can create an InputBox with multiple lines. There are 3 ways to create an InputBox with multiple lines in Excel VBA. We will demonstrate them in the sections below.
Here in the 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.
1. Use InputBox Function to Create an InputBox with Multiple Lines in Excel VBA
We can use the InputBox function to create an InputBox with multiple lines. We need to use some constants inside the InputBox function to separate one line from another.
1.1. Use vbNewLine Constant in InputBox Function
Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
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 of the customers 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
1.2. Use vbCr Constant in InputBox Function
Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
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- Then, 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 any of the customers 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
1.3. Use vbLf Constant in 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- Then, 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 the name of any of the customers 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
1.4. Use vbCrLf Constant in 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 the name of any of the customers in the specified box of the multiline InputBox. Press OK and you will see how good or poor the rating of the customer was.
2. Use 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. Also, as the user selects data from the dataset instead of typing it into the InputBox, Application.InputBox method is less error-prone.
2.1. Use vbNewLine Constant in 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_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.
2.2. Use vbCr Constant in 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.
2.3. Use vbLf Constant in 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_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 then 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.
2.4. Use vbCrLf Constant in 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.
3. Create a Userform to Show an InputBox with Multiple Lines in Excel VBA
We will use the above code in the UserForm to show output. Just follow the steps below to create a UserForm that displays an InputBox with multiple lines:
- Go to Insert > UserForm.
- Create a UserForm. From the ToolBox, you can take a Label to write anything. This is where you can write multiple lines. Then, take a TextBox to take user input just like the InputBox. You can also take 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.
- Here, Label along with 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- Now, 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 |
1. Using 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 then 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- Finally, it 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 type data.
2. Using 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
- Finally, 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
Here are some things to keep in mind while creating an InputBox with multiple lines in Excel VBA:
- Use the concatenation operator & 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
1. 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.
2. 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.
3. 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 Practice Workbook
You can download the practice book here while going through this article.
Conclusion
In this article, we have discussed in detail how we can create Excel VBA InputBox with multiple lines. There are several methods to put multiple lines in an InputBox. We have discussed those methods in detail.
This article will allow users to use Excel more efficiently and effectively. If you have any questions regarding this essay, feel free to let us know in the comments.
