VBA MsgBox Format in Excel – 5 Examples

What Is an Excel VBA MsgBox?

The Excel VBA MsgBox is a built-in function in Microsoft Excel’s Visual Basic for Applications (VBA) programming language, which allows you to display a message box on the screen with a custom message and buttons. The MsgBox function returns a value that corresponds to the button that is clicked. For example, if the user clicks OK, the function returns 1. If the user clicks Cancel, the function returns 2.

Function Objective:

The MsgBox function is used to create a dialog box that returns a value according to the clicked button.

Syntax:MsgBox(Prompt, [Button As VbMsgBoxStyle = vbOkOnly], [Title], [HelpFile], [Context]) As VbMsgBoxResult

Arguments Explanation:

Return Parameter:

The function returns a statement according to the defined buttons in the message box.

Button Constants:

Icon Constans:

Example 1 – Customizing the Title Box of MsgBox Using Excel VBA

Change the title using the following code:

Steps:

• Insert a module and enter the code:

Sub Customize_Title()
MsgBox "Do you want to continue?", _
vbYesNo + vbQuestion, _
"Step 1 of 4"
End Sub

• Click Run.

Read More: How to Use Excel VBA MsgBox Title

Example 2 – Format a VBA MsgBox Initiating a New Line in Excel

Initiate a new line in a MsgBox using the vbNewLine or vbCr constants.

vbNewLine creates a new line in the message box and is equivalent to pressing Enter. vbCr creates a new line and a carriage return in the message box.

Steps:

• Insert a new module and use the code:

Sub New_Line()
MsgBox "What is the total mark of Milner?" _
& vbNewLine & "Click Yes to get the mark", _
vbYesNo + vbQuestion
End Sub

• Click Run.

Example 3 – Initiating Multiple Lines in a VBA MsgBox

To create multiple lines and provide carriage returns, use the vbCr button code.

Steps:

• Open a new module and enter the code.

Sub Multiple_Lines()
OutPut = MsgBox("Which students are from physics department?" _
& vbCr & "What are their names" _
& vbCr & "How much mark they obtained " _
, vbYesNo + vbQuestion)
End Sub

• Click Run or press F5.

Read More: Excel VBA: Show Multiple Values with Multiple Variables in MsgBox

Method 4 – Formatting a VBA MsgBox with Right Aligned Text in Excel

By default the MsgBox will display statements starting from the left. To align those statements to the right:

Steps:

• Open a new module, enter the code, and run the code by pressing F5.

Sub vbMsgBox_Right()
MsgBox "Check out the data", vbMsgBoxRight
End Sub

Example 5 – VBA MsgBox with a from the Right to Left Reading Text System

vbMsgBoxRtlReading button code is a custom constant defined to enable right-to-left reading order.

Steps:

• Open a new module and enter the code.

Sub MsgBoxRtlReading()
MsgBox "Please insert some numeric data to fill dataset", _
vbMsgBoxRtlReading
End Sub

• Click Run.

How to Create an Application Modal MsgBox in Excel

vbApplicationModal is a constant that can be used to specify the type of modal window to be displayed. When a window is displayed in vbApplicationModal mode, the user cannot interact with any other windows in the application until the modal window is closed.

Steps:

• Open a new module and enter the code.

Sub vbApplicationModal_Msgbox()
MsgBox "Thank you for working", vbApplicationModal
End Sub

• Click Run.

How to Create a System Modal MsgBox in Excel

A system modal dialog box is displayed on top of all windows. The user cannot interact with any other windows until the message box is closed.

Steps:

• Open a new module and enter the code.

Sub vbMsgBox_SetForeground()
MsgBox "Please specify the foreground window", vbMsgBoxSetForeground
End Sub

• Click Run or press F5.

How to Create a VBA MsgBox as a Foreground Window in Excel

If you want to display the message box as a foreground window instead of a modal dialog box, you can use the vbMsgBoxSetForeground button code.

Steps:

• Open a new module and enter the code.

Sub vbMsgBox_SetForeground()
MsgBox "Please specify the foreground window", vbMsgBoxSetForeground
End Sub

• Click Run.

How to Display a Message in a VBA MsgBox Based on User Input in Excel

Steps:

• Open a new module and enter the code.

Sub Conditional_VBA_MsgBox()
OutPut = MsgBox("Which department you are looking for?" _
& vbCr & "Do you want the names or IDs" _
, vbYesNo + vbQuestion)
If OutPut = vbYes Then
MsgBox "Please check the dataset"
ElseIf OutPut = vbNo Then
MsgBox "Thank you for visiting"
End If
End Sub

• Click Run.

• Click “Yes”.

• The output statement is displayed according to the IF function.

• Run the code again and click “No” in the MsgBox.

This is the output.

How to Handle Errors in a MsgBox in Excel VBA

Steps:

• Open a new module and run the code without the On Error GoTo statement:

Sub Error_handling_Msgbox()
MsgBox 469 / 0
MsgBox "Error ignored"
End Sub

• Errors are displayed in the first MsgBox.

• Create a new code with the On Error GoTo statement. Click Run.

Sub Error_handling1()
On Error GoTo next_output
MsgBox 469 / 0
next_output:
MsgBox "Error ignored"
End Sub

• The code ignored the first error output and moved on to the next line.

Benefits of VBA MsgBox in Excel

1. User feedback: MsgBox can be used to provide feedback to the user, such as confirming that an action has been completed or informing the user of an error or issue.

2. User input: MsgBox can be used to gather input from the user, such as asking the user to confirm an action or enter data.

3. Customization: MsgBox can be customized with various parameters, such as the message, title, style, and icon.

4. Error handling: MsgBox can be used in conjunction with an error handling code to provide useful feedback when an error occurs.

5. Interactivity: MsgBox can be used to create interactive Excel applications by prompting the user for input and responding to that input in the VBA code.

6. Debugging: MsgBox can be used to debug a VBA code by displaying the value of variables or other information that can help identify and fix errors in the code.

Read More: Excel VBA: Modeless MsgBox

Frequently Asked Questions

1. What are the common values for the “title” argument in a MsgBox?

Ans:

• “Error“- Used for error messages.
• “Warning“- Used for warning messages.
• “Question“- Used for yes/no or true/false questions.
• “Information“- Used for informational messages.

2. How do I create a custom message box in VBA?

Ans: Use the UserForm object. You can add controls: labels, text boxes, and buttons to the UserForm.

Download Practice Workbook

Download the practice workbook here.

Related Articles

• Create VBA MsgBox Custom Buttons in Excel
• Excel VBA: Work with Variable in MsgBox
• How to Use MsgBox and InputBox in VBA Excel