VBA MsgBox Format in Excel (13 Practical Examples)

Looking for ways to format VBA MsgBox in Excel? Then, this is the right place for you.

VBA MsgBox is a built-in function in Visual Basic for Applications (VBA) that allows programmers to display messages and prompt users for input in a message box. A message box is a small dialog box that appears on the screen with a message. Today, I am going to introduce you to various formats of VBA MsgBox including buttons and icons. In addition, you will also get to know about conditional MsgBox and the customization of your own buttons.

In the following, you will find an overview of VBA MsgBox formatting in Excel.


What Is Excel VBA MsgBox?

Excel VBA MsgBox is a built-in function in Microsoft Excel’s Visual Basic for Applications (VBA) programming language. It allows you to display a message box on the screen with a custom message and buttons for the user to respond to. The MsgBox function returns a value that corresponds to the button the user clicked. For example, if the user clicks the OK button, the function returns the value 1. If the user clicks the Cancel button, the function returns the value 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 Button and icon 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 Help Button.
vbDefaultButton1 0 Defines the first button default.
vbDefaultButton2 256 This defines the second button default.
vbDefaultButton3 512 Defines the third button default.
vbApplicationModal 0 Close MsgBox to provide access to Current applications
vbSystemModal 4096 This close MsgBox to provide access to all applications
vbMsgBoxSetForeground 65536 Set a foreground for the msgBox
vbMsgBoxRight 524288 Text alignment from the right side
vbMsgBoxRtlReading 1048576 Provide RTL support

Icon Constans:

Icon Codes Values Description
vbCritical 16 This 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 64 This shows information message icon- The letter ‘i’ in white inside a blue-filled circle.

Read More: VBA MsgBox to Return Values in Excel


Excel VBA MsgBox Format: Customizing Lines in MsgBox

Customizing VBA MsgBox allows you to create a message box that fits the specific needs of your application. You can customize the message that appears in the message box, the buttons that are displayed, the icon that is displayed, and the title of the message box. By customizing the message box, you can make it easier for users to understand what action they need to take or what information they need to know.


1. Customizing Title Box of MsgBox to Format Using Excel VBA

Customizing Title Box of MsgBox to format vba msgbox in Excel

In the previous methods, we have got a default title “Microsoft Excel”. You can change the title using the following code-

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

  • Insert a module and put the code down-
Sub Customize_Title()
MsgBox "Do you want to continue?", _
vbYesNo + vbQuestion, _
"Step 1 of 4"
End Sub
  • Click the Run icon.
  • In conclusion, we have attached a new title at the top of our MsgBox.

Read More: How to Use Excel VBA MsgBox Title


2. Format VBA MsgBox Initiating New Line in Excel

Final result with initiating new line in VBA MsgBox

In VBA, you can initiate a new line in a MsgBox by using the vbNewLine or vbCr constants.

vbNewLine creates a new line in the message box and is equivalent to pressing the Enter key. vbCr creates a new line and a carriage return in the message box. Go through the steps below to learn the simple trick.

Steps:VBA code in a module to initiate new line in MsgBox

  • Insert a new module and write the code-
Sub New_Line()
MsgBox "What is the total mark of Milner?" _
& vbNewLine & "Click Yes to get the mark", _
vbYesNo + vbQuestion
End Sub
  • Therefore, hit the Run icon.
  • Finally, we have got a new line under the previous line.

3. Initiating Multiple Lines in VBA MsgBox

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

For creating multiple lines and providing carriage returns we can use the vbCr button code.

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

  • Similarly, open a new module and write the below code inside the module-
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
  • Now, press the Run icon or hit F5 key from the keyboard.
  • As a result, without any hesitation, our MsgBox is created with multiple lines.

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


4. Formating VBA MsgBox with Right Aligned Text in Excel

Final output with aligned text to right

By default your MsgBox will display specified statements starting from the left. But you might need to align those statements to the right for working advantages. For this you can use the below code-

Steps:VBA code for aligning text to right

  • In the same fashion, open a new module, place the code, and run the code by hitting F5 key from the keyboard.
Sub vbMsgBox_Right()
MsgBox "Check out the data", vbMsgBoxRight
End Sub
  • Finally, you will get the specified statements aligned right. Simple isn’t it?

5. VBA MsgBox with 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 for a message box. Its output would depend on how it has been implemented in your code. The right-to-left reading order is typically used for languages such as Arabic and Hebrew, written from right to left.

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

  • Simply, insert a new module and put the below code down.
Sub MsgBoxRtlReading()
MsgBox "Please insert some numeric data to fill dataset", _
vbMsgBoxRtlReading
End Sub
  • After that, run the code by clicking the Run icon. As a result, you will see the title text is positioned from right to left.

How to Create 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, it means that the user cannot interact with any other windows in the application until the modal window is closed. This is useful for displaying important information or prompts that require the user’s attention before proceeding with a task.

Steps:VBA code for application modal

  • To start with, open a new module and write the below code.
Sub vbApplicationModal_Msgbox()
MsgBox "Thank you for working", vbApplicationModal
End Sub
  • Then, click the Run key to apply the code. Finally, a MsgBox will pop up with the provided statement and the user cannot continue work without responding to the displayed MsgBox.

How to Create System Modal MsgBox in Excel

Final result with system modal

In some situations, a message box is displayed as a system modal dialog, it is displayed on top of all windows in the system and the user cannot interact with any other windows until the message box is closed. This is useful for displaying critical messages or warnings that require the user’s immediate attention.

Steps:VBA code with system modal

  • Similar to the previous methods, open a new module and write the below code down.
Sub vbMsgBox_SetForeground()
MsgBox "Please specify the foreground window", vbMsgBoxSetForeground
End Sub
  • Press the Run icon or F5 key to apply the code. In summary, the MsgBox will display the specified message preventing the user from interacting with any other window on the desktop until they respond to the message box. Thus, you can format VBA MsgBox in Excel.

How to Create VBA MsgBox as Foreground Window in Excel

Final result with foreground window

In VBA, a message box is typically displayed as a modal dialog box which means that it will be displayed in the foreground and the user will not be able to interact with the rest of the application until they respond to the message box. However, 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 module and type the following code.
Sub vbMsgBox_SetForeground()
MsgBox "Please specify the foreground window", vbMsgBoxSetForeground
End Sub
  • Finish applying the code by hitting the Run icon. In conclusion, we have got the statement and the MsgBox is set as a foreground window.

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

A conditional VBA MsgBox is a message box that is displayed only if certain conditions are met. This can be useful if you want to provide the user with information or confirmation based on the values in your Excel workbook. To create a conditional VBA MsgBox, you can use an IF statement to check if the conditions are met. If the conditions are met, the message box is displayed using the MsgBox function. If the conditions are not met, the code continues to execute without displaying the message box. Follow the instructions below-

Steps:VBA code to create a conditional VBA MsgBox for value inputs

  • Inserting a new module and write the code down.
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
  • Next, press the Run icon.

Output with conditional MsgBox

  • Here we have got our first MsgBox. Now let’s click “Yes”.

Output statement of conditional VBA MsgBox

  • As you can see, we have the output statement according to our IF function.

Output with conditional VBA MsgBox

  • Now, let’s run the code again and this time we will click the “No” button from the MsgBox.

Final output with provided statement for condition VBA Msgbox

  • Finally, we have successfully implemented our code where we got our expected output from the second MsgBox. Thus, you can format VBA MsgBox in Excel.

How to Handle Errors of MsgBox in Excel VBA

Final output with handled error inside a VBA Msgbox

VBA On Error GoTo is a method of error handling that allows you to handle runtime errors in your VBA code. The On Error GoTo statement is followed by a label that specifies the error handling code to be executed if an error occurs. The label is defined by a line of code that begins with a colon sign (:) and a name that identifies the error handling code.

Steps:VBA code to handle error on MsgBox

  • First, let’s open a new module and run the below code without the On Error GoTo statement and let’s find out what happens.
Sub Error_handling_Msgbox()
MsgBox 469 / 0
MsgBox "Error ignored"
End Sub

MsgBox displaying run-time error

  • As you can see, we will find errors for the first MsgBox.

VBA code inside a module for error handling

  • This time we will create a new code with On Error GoTo statement to avoid errors. Now, run the code by clicking Run icon
Sub Error_handling1()
On Error GoTo next_output
MsgBox 469 / 0
next_output:
MsgBox "Error ignored"
End Sub

Error handled through code

  • So, you will get a result where the code ignored the first error output and moved on to the next line MsgBox result.

Benefits of VBA MsgBox in Excel

VBA MsgBox is a useful tool for displaying messages and gathering user input in Excel applications. Here are some of the benefits of using MsgBox in VBA

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. This allows you to create message boxes that are tailored to your specific needs.

4. Error handling: MsgBox can be used in conjunction with error handling code to provide useful feedback to the user 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 your VBA code.

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

Overall, using MsgBox in VBA can help make your Excel applications more interactive, user-friendly, and robust by providing feedback and gathering input from the user.

Read More: Excel VBA: Modeless MsgBox


Takeaways from This Article

  • This article demonstrates Excel MsgBox formatting.
  • We described all the buttons available with VBA MsgBox formatting.
  • We demonstrated multiple icons in MsgBox.
  • Then, we have discussed how to handle errors of VBA MsgBox.
  • We have shown you the process to create your own customized buttons.
  • We have covered ways of changing the default buttons of MsgBox.

Frequently Asked Questions

1. What is a VBA MsgBox?

Ans: A VBA MsgBox is a pop-up dialog box that displays a message and provides options for the user to respond.

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

Ans: Some common values for the “title” argument are-

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

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

Ans: You can create a custom message box in VBA by using the UserForm object. You can add controls such as labels, text boxes, and buttons to the UserForm, and then use VBA code to display the UserForm and handle the user’s response.


Download Practice Workbook

You can download our practice workbook from here for free.


Conclusion

In conclusion, the MsgBox function in VBA is a very useful tool for displaying messages and interacting with the user. By using the appropriate format, you can customize the appearance and behavior of the message box to suit your needs. In addition to the buttons parameter, you can also specify other parameters to customize the behavior and appearance of the message box, such as the icon to be displayed, the default button, and the help file and context. Hope this article will help you to learn how you can format VBA MsgBox in Excel. Take a tour of the practice workbook and download the file to practice by yourself. Please inform us in the comment section about your experience. We are always responsive to your queries. Stay tuned and keep learning.


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