Understanding the VBA MsgBox Function: A Comprehensive Guide

VBA is a programming language that is used in Microsoft Office to automate tasks, execute complex calculations, and efficiently manipulate data. One of the essential features of VBA is the VBA MsgBox which allows users to display a message in a dialog box during the execution of VBA code.

In this article, we will be covering an exhaustive guide on how to display a message, customize a message, add icons, add/change buttons, and much more.

Download the Excel Workbook below to follow along and understand how to use VBA MsgBox Function in Excel –
download excel workbook VBA-MsgBox.xlsm

 

Introduction to VBA Msgbox

The VBA MsgBox function displays a message in a dialog box, waits for user input in the form of a button click, and then returns an Integer value indicating the specific button clicked by the user. The VBA MsgBox consists of four components, namely:

1. Title – Used to provide a heading of the message’s content.
2. Prompt – The main message displayed to the user.
3. Button – Enables the user to select an appropriate response.
4. Close – Allows the user to close the message box when needed.

The syntax of VBA Msgbox is –

MsgBox(prompt, [buttons], [title], [helpfile], [context])

where,

• prompt – This is the message that you want to display to the user in the dialog box. It can be an instruction, critical information, or a question to the user. This is the primary content of the message box and is mandatory for the MsgBox function to work.
• buttons – This allows the user to choose an appropriate response. VBA can be constants that are pre-defined and can be used to specify the type of button. Some of the examples of buttons that can be used are OK, Yes, No, Cancel, Retry, etc. This is an optional argument and if omitted, VBA displays the OK button as a default.
• title – This is used to display what the message is about and it appears on the title bar of the dialog box. Adding a descriptive title can make the message box more informative and user-friendly. If omitted, it displays the name of the application i.e. Microsoft Excel as default.
• helpfile and context – These are optional parameters used for context-sensitive help.

 

Let us look at a simple example to understand better.

Sub BasicMessageBox()
MsgBox "Welcome to VBA MsgBox tutorial by MyExcelOnline"
End Sub

This is how the VBA MsgBox will look like –

You can see that the default title is Microsoft Excel and the default button is OK. These values are applied because you omitted them while typing the code.

Now, let us look at further customizations that can be done to the MsgBox.

 

Customize MsgBox Buttons

You can use one or more buttons in your MsgBox as per your requirement. The default button is OK in VBA MsgBox but you can use any of the following buttons –

Constant	Description
vbOKOnly	Display OK button only.
vbOKCancel	Display OK and Cancel buttons.
vbAbortRetryIgnore	Display Abort, Retry, and Ignore buttons.
vbYesNoCancel	Display Yes, No, and Cancel buttons.
vbYesNo	Display Yes and No buttons.
vbRetryCancel	Display Retry and Cancel buttons.

Based on the input provided by the user, the VBA code takes the next step. For example, if you select OK the code will continue but if you select Cancel the code will stop running.

Let us try an example of how different buttons look in the message box.

Sub MessageBox1()
MsgBox "Welcome to VBA MsgBox tutorial by MyExcelOnline. Do you want to continue?",vbYesNo
End Sub

Two buttons Yes and No have been added to the message box.

 

Add Icons to MsgBox

You can even add custom icons to your message box like a question mark or an exclamation sign to make the message box user-friendly. There are four kinds of icons available – vbCritical, vbQuestion, vbExclamation, and vbInformation.

Constant	Description
vbCritical	Display Critical Message icon.
vbQuestion	Display Warning Query icon.
vbExclamation	Display Warning Message icon.
vbInformation	Display Information Message icon.

You can show both buttons and icons together by entering a plus (+) sign in between the two.

Sub MessageBox2()
MsgBox "Welcome to VBA MsgBox tutorial by MyExcelOnline. Do you want to continue?",vbYesNo + vbQuestion
End Sub

A Question Mark has been added to the message box.

 

Add Title to MsgBox

In the above examples, the default title “Microsoft Excel” is displayed. Let us change that.

Sub MessageBox3()
MsgBox "Welcome to VBA MsgBox tutorial by MyExcelOnline. Do you want to continue?",vbYesNo + vbQuestion, "Please Choose"
End Sub

You can see that the title is now “Please Choose”.

 

Run VBA MsgBox code

Now that you are comfortable with writing the code, let us see how to actually use it in Excel. Follow the steps below to successfully run the message box code –

STEP 1: Open the Workbook and press Alt + F11 to open the VBA editor.

STEP 2: Click on Insert > Module.

STEP 3: Write the code –

STEP 4: Press the Run icon.

You can also go to Developer Tab > Macros.

Now, select the Macro that you want to run and press Run.

This is how the VBA MsgBox will look like –

 

Conclusion

The VBA MsgBox function is an essential tool for displaying messages, prompting user input, and confirming critical decisions in VBA projects. By understanding its syntax, button types, icon options, and practical examples, you can effectively use the MsgBox function to improve the user experience and enhance the functionality of your VBA applications.

Click here to learn more about VBA MsgBox in Excel.