Understanding the VBA MsgBox Function: A Comprehensive Guide | MyExcelOnline

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 

 

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:

VBA MsgBox

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 –

Understanding the VBA MsgBox Function: A Comprehensive Guide | MyExcelOnline

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 AbortRetry, and Ignore buttons.
vbYesNoCancel Display YesNo, 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.

Understanding the VBA MsgBox Function: A Comprehensive Guide | MyExcelOnline

 

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.

Understanding the VBA MsgBox Function: A Comprehensive Guide | MyExcelOnline

 

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

VBA MsgBox

 

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.

VBA MsgBox

STEP 2: Click on Insert > Module.

VBA MsgBox

STEP 3: Write the code –

VBA MsgBox

STEP 4: Press the Run icon.

VBA MsgBox

You can also go to Developer Tab > Macros.

VBA MsgBox

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

VBA MsgBox

This is how the VBA MsgBox will look like –

VBA Msgbox 12

 

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.

HELPFUL RESOURCES:

JOIN OUR FREE EXCEL COURSES BELOW
Click here to access these FREE Excel courses!

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin