Easy VBA Message Box Tutorial

Message boxes in Excel are a great way of communicating with the end user. They can improve the user’s experience by adding interactivity to your worksheet.

For example, a message box allows you to tell the end user what the macro has done or hasn’t done. It helps to direct the user and allows them to provide a response by clicking buttons and interacting with Excel.

If you've ever wanted to learn about message boxes and how to create your own in Excel, this article explains everything you will need to know.  Let's start with the definition of a message box, or, as you’ll often see in VBA code, msgbox.

What is a MsgBox in VBA?

A message box is a pop up box that appears to the user. For example, you might try to close a file and a VBA display message will appear to ask if you’re sure you would like to save the file before closing. 

Similarly, when you delete a worksheet, a VBA display message will appear asking you to confirm that you would like to delete the worksheet. Those are just a couple of VBA Msgbox examples that you’ll see when working in Excel.

How do I create a Msgbox?

In Excel, you have the ability to create your own message boxes using the Excel VBA Editor. 

Use the MsgBox VBA function, fill in the required information that the function needs to run properly and the VBA msgbox will appear on screen like the ones you will have seen before when you’re working in Excel.

To create a msgbox in VBA, type:

MsgBox(

The intellisense appears in yellow, which tells us what parameters the function needs to work. Parameters are separated by commas.

Parameter definitions

1. Prompt is the first parameter and is not wrapped within a set of square brackets. This means it's a compulsory parameter and has to be filled in. Prompt is the message that will appear in the message box on screen.

2. Buttons as VbMsgBoxStyle = vbOKOnly refer to the buttons or icons that appear in the VBA message box.

Examples of the most common VBA msgbox options include:

 Icons

• Critical icon
• Exclamation Mark icon
• Question Mark icon
• Information icon

Buttons

• vbOKOnly - Displays OK button only.
• vbOKCancel - Displays OK and Cancel buttons.
• vbAbortRetryIgnore  - Displays Abort, Retry, and Ignore buttons.
• vbYesNoCancel - Displays the Excel VBA msgbox yes no and Cancel buttons.
• vbYesNo - Displays the VBA msgbox yes no buttons
• vbRetryCancel - Displays Retry and Cancel buttons.

 

3. The Title parameter is the title that appears at the top of the message box.

4. HelpFile and Context are parameters that aren’t commonly used, so we won’t explain them here.

VBA MsgBox example

Let’s go through an example, fill in each of the parameters, and get a message box to pop up on the screen.

The first parameter is Prompt and accepts any text wrapped in quotation marks. For example,

Msgbox “I want this message to pop up on the screen”

As mentioned before, this is the only compulsory parameter, so it could be left like this and it would work just fine.

Optional parameters

To make our msgbox look more appealing, we can add icons and buttons. To do this, fill in the second parameter Buttons As VbMsgBoxStyle = vbOKOnly. A list of VBA msgbox options will appear for you to choose from.

For a full list of options, see the Settings section in Microsoft's list of buttons and icons.

You have the ability to select an icon and a button by adding a + sign like this.

But you can’t add two sets of buttons or two sets of icons. So the following would work because we have entered a single icon and a single set of buttons:

MsgBox "I want this message to pop up on the screen", vbCritical + vbYesNo

But the one below would not work because it has two icons (vbCritical + vbInformation).

MsgBox "I want this message to pop up on the screen", vbCritical + vbInformation

The message box does appear, but as you can see below, the icons don’t.

The final parameter is the Title parameter. From the above screenshots, you can see the title of the message box defaults to “Microsoft Excel”. This is because it hasn’t been filled in yet. Similar to before, you can move on to the third parameter by adding another comma as shown below.

MsgBox "I want this message to pop up on the screen", vbCritical + vbYesNo,

The Title parameter has turned to bold, which shows that’s the one to be filled in next. Like the Prompt parameter, Title accepts a string of text wrapped in quotation marks like this:

MsgBox "I want this message to pop up on the screen", vbCritical + vbYesNo, "GoSkills Message Box Training"

When this line of code is played, the following message box appears:

As you can see, there’s a title at the top of the message box that says “GoSkills Message Box Training”. This works well, and you can use a similar line of code throughout your projects to communicate with the user what’s going on.

VBA Message Box Yes/No

Congrats on getting this far!

The problem with this is that when the Excel VBA message box Yes/No pop-up box appears and the user clicks either the yes or no button, nothing happens. The message box disappears, and any code after that would just run as normal.

What would make this better is if when the user does click the Yes or No button, you can control what lines of code run next. Wouldn’t it be great if there was a way of capturing which button the user clicks so that Excel knows what to do next? To do this, we need to capture which button is clicked in a variable. The code will need to be changed slightly.

First, declare a variable that will hold which button the user clicks.

 

Dim Msgboxresult As VbMsgBoxResult

 

Assign the result to the Msgboxresult variable by using an equal sign and wrapping the function in a set of parentheses as shown below.

Msgboxresult = MsgBox("I want this message to pop up on the screen", vbCritical + vbYesNo, "Go Skills Message Box Training")

 

You can step through the code by line, by pressing F8 each time to run each line individually. The line to be run is highlighted in yellow as shown below.

 

Press the F8 button again.

Hover over the Msgboxresult variable with the mouse and you can see that it assigns Msgboxresult the value 6.

For a full list of values and descriptions, see the Return values section in Microsoft return values. You can see that the value 6 is the value for the constant vbyes, which is the "Yes" button. In this example, the "Yes" button was clicked.

 

Vbyes or the value 6 can be used to reference the Yes button. However, using vbyes is strongly recommended because it makes the code easier to understand. Using the same technique, when the "No" button is clicked, the Msgboxresult = 7, which is the value for the constant vbno or the "No" button.

Armed with this knowledge, you've captured which button the user has clicked and can write code based on the user’s response. For example:

Sub TestMessageBox()



Dim Msgboxresult As VbMsgBoxResult

     Msgboxresult = MsgBox("I want this message to pop up on the screen", vbCritical + vbYesNo, "Go Skills Message Box Training")

    If Msgboxresult = vbYes Then

        MsgBox "The user clicked yes!", vbInformation

    Else

        MsgBox "The user clicked no!", vbCritical

    End If

  End Sub

In this example, a message box appears with a critical icon and the Yes/No buttons. 

• When the user clicks the Yes button, another message box appears that says “The user clicked Yes!” with an information icon.
• When the user clicks the No button, another message box appears that says “The user clicked No!” with a critical icon.

This example demonstrates a powerful technique of how you can get Excel to perform different actions depending on which button the user has clicked, creating two-way communication between Excel and the user using message boxes.

Don’t stop now!

Ready to explore more in the world of VBA in Excel? Check out our expert-led course on Excel Macros and VBA. Try for free.

Ready to become a certified Excel ninja?

Take GoSkills' Macros & VBA course today!

Start free trial