Excel VBA: Create New Line in MsgBox (6 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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:

  • Press Alt + F11 to open the VBA window.
  • Then click as follows to insert a module: Insert ➤ Module.

Use vbNewLine to Add New Line in MsgBox Using Excel VBA

  • Later, type the following codes in it-
Sub NewLine_vbNewLine()
MsgBox "Hello!" & vbNewLine & "Welcome to ExcelDemy"
End Sub

Use vbNewLine to Add New Line in MsgBox Using Excel VBA

  • Then go back to your sheet and click as follows to open the Macros dialog box: Developer ➤ Macros.

Use vbNewLine to Add New Line in MsgBox Using Excel VBA

  • 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 VBAvbCrLf to create a new line in MsgBox. It will also add a new line between the two consecutive lines.

Steps:

Sub NewLine_vbCrLf()
MsgBox "Hello!" & vbCrLf & vbCrLf & "Welcome to ExcelDemy"
End Sub

Use vbCrLf to Create New Line in MsgBox Using VBA in Excel

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:

Sub NewLine_vbLf()
MsgBox "Hello!" & vbLf & "Welcome to ExcelDemy"
End Sub

Insert vbLf to Add New Line in MsgBox Using Excel VBA

Insert vbLf to Add New Line in MsgBox Using Excel VBA

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:

Sub NewLine_Chr()
MsgBox "Hello!" & Chr(13) & Chr(10) & "Welcome to ExcelDemy"
End Sub

Apply Chr to Create New Line in MsgBox Using VBA in Excel

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:

Sub NewLine_within_Macro()
MsgBox "Hello!" & vbCrLf & vbCrLf & _
"Welcome to ExcelDemy"
End Sub

Add New Line within the Macro in Excel VBA

Add New Line within the Macro in Excel VBA

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.

Embed VBA to Add New Lines in MsgBox Using Button

  • Click as follows: Developer ➤ Insert and then select the rectangular box from the Form Controls section.

Embed VBA to Add New Lines in MsgBox Using Button

  • 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.

Embed VBA to Add New Lines in MsgBox Using Button

  • Then right-click your mouse on the button and select Edit Text from the context menu to set the button name.

Embed VBA to Add New Lines in MsgBox Using Button

  • 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

Embed VBA to Add New Lines in MsgBox Using Button

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.

Embed VBA to Add New Lines in MsgBox Using Button

  • 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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel projects.

Tags:

Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo