Example 1 – Use vbNewLine to Add New Line in MsgBox Using Excel VBA
We will show “Hello!” in the first line and “Welcome to ExcelDemy” in the second line.
Steps:
- Press Alt + F11 to open the VBA window.
- Go to Insert ➤ select Module.
- Enter the following code.
Sub NewLine_vbNewLine()
MsgBox "Hello!" & vbNewLine & "Welcome to ExcelDemy"
End Sub
- Go to the Developer tab➤ select Macros.
- Select the Macro name as specified in the codes.
- Press Run.
vbNewLine will show the output in two lines.
Example 2 – Use vbCrLf to Create New Line in MsgBox Using VBA in Excel
Steps:
- Follow the first two steps from the first method to insert a module in the VBA.
- Enter the following code.
Sub NewLine_vbCrLf()
MsgBox "Hello!" & vbCrLf & vbCrLf & "Welcome to ExcelDemy"
End Sub
- Follow the fourth step from the first method to open the Macro dialog box.
- Select the Macro name and press Run.
The constant vbCrLf will add a new line with a gap line.
Read More: Excel VBA: Develop and Use a Yes No Message Box
Example 3 – Insert vbLf to Add New Line in MsgBox Using Excel VBA
Steps:
- Follow the first two steps from the first method to insert a module in the VBA.
- Enter the following code.
Sub NewLine_vbLf()
MsgBox "Hello!" & vbLf & "Welcome to ExcelDemy"
End Sub
- Follow the fourth step from the first method to open the Macro dialog box.
- Select the Macro name and press Run.
You will get the desired output.
Read More: How to Show Range Address with MsgBox in Excel VBA
Example 4 – Apply Chr to Create New Line in MsgBox Using VBA in Excel
Steps:
- Insert a module in the VBA.
- Enter the following code.
Sub NewLine_Chr()
MsgBox "Hello!" & Chr(13) & Chr(10) & "Welcome to ExcelDemy"
End Sub
- Open the Macro dialog box.
- Select the Macro name and press Run.
Read More: Excel VBA Code to Click OK on Message Box Automatically
Example 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:
- Insert a module in the VBA.
- Enter the following code.
Sub NewLine_within_Macro()
MsgBox "Hello!" & vbCrLf & vbCrLf & _
"Welcome to ExcelDemy"
End Sub
- Open the Macro dialog box.
- Select the Macro name and press Run.
It will add a new line with a gap line between the lines.
Read More: Excel VBA MsgBox Examples
Example 6 – Embed VBA to Add New Lines in MsgBox Using Button
6.1 Button for Single Line
We have made a dataset that represents three cells to give input – Last Name, Address and Phone number. When we click the button, it will check the cells and if gets an empty cell, it will show the message for that cell.
- Go to the Developer tab➤ select Insert then select the rectangular box from the Form Controls section.
- You will get a plus sign in your cursor, left-click and drag on the sheet to set the button size.
- Right-click on the button and select Edit Text from the context menu to set the button name.
- Enter a Name and click anywhere on the sheet.
- Right-click on the button and select Assign Macro from the context menu to set a Macro.
- Give a Macro name and press New.
- Enter the following code.
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
- We have created a Sub procedure SingleLine_Button.
- We have declared a variable WS as Worksheet.
- We have 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 it will show the corresponding message through MsgBox.
- Go back to your sheet and click the button.
As the Last Name field is empty, it is showing the notification message.
- Press OK. It will check the second field.
The second field is empty too, so it added a new line to notify it.
We have filled the first field. When we clicked the button, 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.
- Follow the first 6 steps from the previous section to add a button and assign a macro.
- Enter the following code 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
- We have created a Sub procedure Multiple_Line_Button.
- We have declared some variables WS As Worksheet and Last_Name, Address, Phone, Error_msg As
- We have used Len and Range to set the fields.
- We have used If statements to check whether the fields are empty or not. If it is filled then it will skip it. If not, then will show messages through MsgBox.
- Return to the sheet and click the button.
We can see that it is showing three lines for the three fields as they are all empty.
We have filled the first field. Now, it is only showing messages for the next 2 fields.
Read More: VBA MsgBox That Will Automatically Disappear in Excel
Download Practice Workbook
