We often use MsgBox in VBA to show the notification after running the codes. Sometimes a single line can’t show the output properly. Then we need to add new lines. So from this article, you will learn 6 useful Macros to add a new line in MsgBox using Excel VBA.
How to Create New Line in MsgBox Using Excel VBA: 6 Examples
1. Use vbNewLine to Add New Line in MsgBox Using Excel VBA
Here, we’ll use vbNewLine in VBA Macro to add a line in MsgBox. I’ll show “Hello!” in the first line and “Welcome to ExcelDemy” in the second line.
Steps:
- Later, type the following codes in it-
Sub NewLine_vbNewLine()
MsgBox "Hello!" & vbNewLine & "Welcome to ExcelDemy"
End Sub
- Select the Macro name as specified in the codes.
- Finally, just press Run.
Now have a look, vbNewLine is showing the output in two lines.
2. Use vbCrLf to Create New Line in MsgBox Using VBA in Excel
Now we’ll use another constant of VBA– vbCrLf to create a new line in MsgBox. It will also add a new line between the two consecutive lines.
Steps:
- First, follow the first two steps from the first method to insert a module in the VBA
- Then write the following codes in it-
Sub NewLine_vbCrLf()
MsgBox "Hello!" & vbCrLf & vbCrLf & "Welcome to ExcelDemy"
End Sub
- Next, follow the fourth step from the first method to open the Macro dialog box.
- Select the Macro name and press Run.
The constant vbCrLf has added a new line with a gap line too.
Read More: Excel VBA: Develop and Use a Yes No Message Box
3. Insert vbLf to Add New Line in MsgBox Using Excel VBA
Let’s use another constant- vbLf to add a new line in MsgBox in Excel VBA.
Steps:
- Follow the first two steps from the first method to insert a module in the VBA
- Later, write the following codes in it-
Sub NewLine_vbLf()
MsgBox "Hello!" & vbLf & "Welcome to ExcelDemy"
End Sub
- Then follow the fourth step from the first method to open the Macro dialog box.
- Later, select the Macro name as mentioned in the codes and press Run.
And soon after you will get the desired output.
Read More: How to Show Range Address with MsgBox in Excel VBA
4. Apply Chr to Create New Line in MsgBox Using VBA in Excel
Here, we’ll use two constants of VBA- Chr(13) & Chr(10) to add lines.
Steps:
- Start with following the first two steps from the first method to insert a module in the VBA
- Then write the following codes in it-
Sub NewLine_Chr()
MsgBox "Hello!" & Chr(13) & Chr(10) & "Welcome to ExcelDemy"
End Sub
- After that follow the fourth step from the first method to open the Macro dialog box.
- Then select the assigned Macro name and press Run.
See that we have got the same output.
Read More: Excel VBA Code to Click OK on Message Box Automatically
5. Add New Line within the Macro in Excel VBA
In the previous methods, we didn’t break the line in the code. Here, we’ll break and add lines within the codes.
Steps:
- First, follow the first two steps from the first method to insert a module in the VBA
- Next, write the following codes in it-
Sub NewLine_within_Macro()
MsgBox "Hello!" & vbCrLf & vbCrLf & _
"Welcome to ExcelDemy"
End Sub
- Later, follow the fourth step from the first method to open the Macro dialog box.
- Then just select the Macro name and press Run.
Now see, that macro has added a new line keeping a gap line between the lines.
Read More: Excel VBA MsgBox Examples
6. Embed VBA to Add New Lines in MsgBox Using Button
In our very last method, we’ll do the task in a bit different way. We’ll set a button to add lines in MsgBox.
6.1 Button for Single Line
First, we’ll make a button for adding a single line. For that, I have made a dataset that represents three cells to give input Last Name, Address, and Phone number. When we’ll click the button, it will check the cells and if gets an empty cell then will show the message for that cell.
- Click as follows: Developer ➤ Insert and then select the rectangular box from the Form Controls section.
- Soon after, you will get a plus sign in your cursor, left-click your mouse and drag on the sheet to set the button size.
- Then right-click your mouse on the button and select Edit Text from the context menu to set the button name.
- Type the Name and click anywhere on the sheet.
- Again right-click your mouse on the button and select Assign Macro from the context menu to set a Macro.
- At this moment, give a Macro name and press New.
- Then type the following codes–
Private Sub SingleLine_Button()
Dim WS As Worksheet
Set WS = Sheets("Single Line")
If WS.Range("C4").Value = "" Then
MsgBox "Please Insert Last Name!"
End If
If WS.Range("C5").Value = "" Then
MsgBox "Please Insert Address!"
End If
If WS.Range("C6").Value = "" Then
MsgBox "Please Insert Phone Number!"
End If
End Sub
Code Breakdown
- First, I created a Sub procedure SingleLine_Button.
- Then declared a variable WS as Worksheet.
- Then used three IF statements to check the cells, if the cell is filled with value then it will ignore it and if gets an empty cell then will show the corresponding message through MsgBox.
- Later, just go back to your sheet and click the button.
As the Last Name field is empty that’s why it is showing the notification message.
- Press OK and then it will check the second field.
The second field is empty too so it added a new line to notify it.
Here, I filled the first field and clicked the button, and see that it skipped that field and jumped to the second field to show the message.
6.2 Button for Multiple Lines
By assigning this button, we’ll be able to add multiple lines at a time in the message box.
- First, follow the first 6 steps from the previous section to add a button and assign a macro.
- Then type the following codes in the macro-
Sub Multiple_Line_Button()
Dim WS As Worksheet
Set WS = Sheets("Multiple Line")
Dim Last_Name, Address, Phone, Error_msg As String
Last_Name = Len(WS.Range("C4"))
Address = Len(WS.Range("C5"))
Phone = Len(WS.Range("C6"))
If Last_Name = 0 Then
Error_msg = "Please Insert Last Name!"
End If
If Address = 0 Then
Error_msg = Error_msg & vbNewLine & "Please Insert Address!"
End If
If Phone = 0 Then
Error_msg = Error_msg & vbNewLine & "Please Insert Phone Number!"
End If
If Error_msg <> "" Then
MsgBox Error_msg, vbOKOnly, Title:="Important Caution!"
Exit Sub
End If
End Sub
Code Breakdown
- Here, I created a Sub procedure Multiple_Line_Button.
- Then declared some variables WS As Worksheet and Last_Name, Address, Phone, Error_msg As
- Later, used Len and Range to set the fields.
- Finally, used If statements to check whether the fields are empty or not. If it is filled then will skip it and if not then will show messages through MsgBox.
- Finally, just return to the sheet and click the button.
And see it is showing three lines for the three fields as they all are empty.
I filled the first field and now see, it is only showing messages for the next 2 fields.
Read More: VBA MsgBox That Will Automatically Disappear in Excel
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
Conclusion
I hope the procedures described above will be good enough to create a new line in MsgBox using Excel VBA. Feel free to ask any question in the comment section and please give me feedback.
