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:Syntax of Excel VBA MsgBoxMsgBox(Prompt, [Button As VbMsgBoxStyle = vbOkOnly], [Title], [HelpFile], [Context]) As VbMsgBoxResult

Arguments Explanation:

Argument Required/Optional Explanation
Prompt Required A statement that will be shown in the message box
[Buttons] Optional Codes to display buttons and icons in the message box.
[Title] Optional Title or name of the message box.
[HelpFile] Optional Index or link which is assigned to the Help button in the message box.
[Context] Optional Index or specific topic number of the Help file.

Return Parameter:

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

Button Constants:

Button Codes Values Description
vbOKOnly 0 Shows the Ok button only (Default).
vbOKCancel 1 Displays OK and Cancel buttons.
vbYesNo 3 Returns Yes and No buttons.
vbYesNoCancel 4 Shows Yes, No and Cancel buttons.
vbAbortRetryIgnore 2 Displays Abort, Retry and Ignore buttons.
vbRetryCancel 5 Shows Retry and Cancel buttons.
vbMsgBoxHelpButton 16384 Returns a Help Button.
vbDefaultButton1 0 Defines the first default button.
vbDefaultButton2 256 Defines the second default button.
vbDefaultButton3 512 Defines the third default button.
vbApplicationModal 0 Closes MsgBox to provide access to current applications
vbSystemModal 4096 Closes MsgBox to provide access to all applications
vbMsgBoxSetForeground 65536 Sets a foreground for the msgBox
vbMsgBoxRight 524288 Text alignment from the right side
vbMsgBoxRtlReading 1048576 Provides RTL support

Icon Constans:

Icon Codes Values Description
vbCritical 16 Displays the critical message icon- Thin white cross inside a red-filled circle.
vbQuestion 32 Shows the critical message icon- Thin white cross inside a red-filled circle.
vbExclamation 48 Displays the warning message icon- Black exclamatory symbol inside a yellow-filled triangle.
vbInformation 64S hows information message icon- The letter ‘i’ in white inside a blue-filled circle.

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

Customizing Title Box of MsgBox to format vba msgbox in Excel

Change the title using the following code:

Steps:VBA code to customize title to format vba msgbox in excel

  • 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

Final result with initiating new line in VBA MsgBox

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:VBA code in a module to initiate new line in MsgBox

  • 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

Final result with initiating multiple lines inside a VBA MsgBox in Excel

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

Steps:VBA code in a module to initiate multiple line to Format MsgBox in Excel

  • 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

Final output with aligned text to right

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

Steps:VBA code for aligning text to right

  • 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

Final result by aligning title to right side

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

Steps:VBA code to align title text starting from right side

  • 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

Final output with application modal

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:VBA code for application modal

  • 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

Final result with system modal

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:VBA code with system modal

  • 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

Final result with foreground window

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:VBA code for setting MsgBox foreground

  • 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:VBA code to create a conditional VBA MsgBox for value inputs

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

Output with conditional MsgBox

  • Click “Yes”.

Output statement of conditional VBA MsgBox

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

Output with conditional VBA MsgBox

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

Final output with provided statement for condition VBA Msgbox

This is the output.


How to Handle Errors in a MsgBox in Excel VBA

Final output with handled error inside a VBA Msgbox

Steps:VBA code to handle error on MsgBox

  • 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

MsgBox displaying run-time error

  • Errors are displayed in the first MsgBox.

VBA code inside a module for error handling

  • 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

Error handled through code

  • 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

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo