Pinterest Pixel

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,... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

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 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:

Understanding the VBA MsgBox Function: A Comprehensive Guide

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.
See also  Convert Chart into Image Using Macros In Excel

 

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

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.

See also  Print the Selected Area Using Macros In Excel

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

 

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

 

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

See also  Create a Table of Contents Using Macros In Excel

Understanding the VBA MsgBox Function: A Comprehensive Guide

 

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.

Understanding the VBA MsgBox Function: A Comprehensive Guide

STEP 2: Click on Insert > Module.

Understanding the VBA MsgBox Function: A Comprehensive Guide

STEP 3: Write the code –

Understanding the VBA MsgBox Function: A Comprehensive Guide

STEP 4: Press the Run icon.

Understanding the VBA MsgBox Function: A Comprehensive Guide

You can also go to Developer Tab > Macros.

Understanding the VBA MsgBox Function: A Comprehensive Guide

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

Understanding the VBA MsgBox Function: A Comprehensive Guide

This is how the VBA MsgBox will look like –

Understanding the VBA MsgBox Function: A Comprehensive Guide

 

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.

See also  Highlight Top 10 Values of Selection Using Macros In Excel

Click here to learn more about VBA MsgBox in Excel.

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

Understanding the VBA MsgBox Function: A Comprehensive Guide | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!