Excel VBA InputBox with Multiple Lines (3 Examples)

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])

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 Sub

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")

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

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 Sub

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")

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 Sub

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 Sub

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")

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 Sub

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 Sub

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")

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 Sub

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 Sub

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")

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 Sub

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 Sub

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")

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 Sub

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 Sub

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")

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 Sub

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 Sub

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")

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 Sub

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.

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")

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

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.

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 Sub

Sub InputBox_SpecificData()
Dim customer_name As String

customer_name = InputBox("Please insert a valid customer name" & vbNewLine & "from the survey table")

If IsNumeric(customer_name) Then
        MsgBox "You did not insert a valid name!", vbInformation
End If
End Sub

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 Sub

Sub Application_InputBox_SpecificData()
Dim customer_score As Integer
Dim myRng As Range
Set myRng = Range("B4:F14")

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 Sub

Sub MsgBox_MultipleLines()
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 "Information of the customer:" & vbCr & _
"Name = " & customer_name & vbCr & _
"Age = " & age & vbCr & "Gender = " & gender
End Sub

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 Sub

Sub InputBox_MultipleInputs()
Dim myRng, info As Range
Set myRng = Range("B15:F15")
Set info = Range("B4:F4")

For i = 1 To 5
        myRng.Cells(i) = InputBox("Please insert the following information :" & vbLf & info(i))
    Next i
End Sub

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.

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