Excel VBA MsgBox - Message Box Macro

Add to Favorites
Author: | Edits: don

Create a pop-up message box in Excel using VBA Macros. This allows you to show a message to the user and to get input back, depending on which buttons were clicked in the pop-up message.

To output a message in Excel, we use the MsgBox function. Below, you will find many examples that should suit your needs.

Sections:

Syntax

Example 1 - Output Basic Text

Example 2 - Add Buttons to the Message Box

Example 3 - Figure Out Which Button Was Clicked

Example 4 - Do Something After the User Clicks a Button

Example 5 - Change MsgBox Appearance

Notes

Syntax

MsgBox(prompt, [buttons], [title], [helpfile], [context])
Argument Description
Prompt

The text that will be in the message box. This is the only required argument for the MsgBox function.

[Buttons]

Allows you to display different buttons and icons in the message box.

[Title]

Text that appears in the title bar of the message box.

[Helpfile]

Not needed. Allows for a specific help file to be used.

[Context]

Not needed. Required if the helpfile argument is used.

[] means it is an optional argument.

Button Arguments

These are the values that can be entered for the buttons argument. You will use the VB Codes to add them to the message box. Examples below will include some of these options so you can better understand them.

VB Code Description Value

vbOKOnly

OK Button. Default.

0

vbOKCancel

OK and Cancel buttons.

1

vbAbortRetryIgnore

Abort, Retry, and Ignore buttons.

2

vbYesNoCancel

Yes, No, and Cancel buttons.

3

vbYesNo

Yes and No buttons.

4

vbRetryCancel

Retry and Cancel buttons.

5

vbCritical

Displays the Critical Message icon in the message box window.

16

vbQuestion

Displays the Warning Query icon in the message box window.

32

vbExclamation

Displays the Warning Message icon in the message box window.

48

vbInformation

Displays the Information Message icon in the message box window.

64

vbDefaultButton1

Selects the first button in the message box by default.

0

vbDefaultButton2

Selects the second button in the message box by default.

256

vbDefaultButton3

Selects the third button in the message box by default.

512

vbDefaultButton4

Selects the fourth button in the message box by default.

768

vbApplicationModal

Application modal; the user must respond to the message box before continuing work in Excel.

0

vbSystemModal

System modal; all applications are suspended until the user responds to the message box.

4096

VbMsgBoxSetForeground

Specifies the message box window as the foreground window

65536

vbMsgBoxRight

Text is right aligned

524288

Values Returned from Button Clicks in the Message Box

When a user clicks one of the buttons that are in the message box, a value will be returned to VBA; that value corresponds to the button that was clicked and is listed below.

Button Clicked Returned Constant Returned # Value

OK

vbOK

1

Cancel

vbCancel

2

Abort

vbAbort

3

Retry

vbRetry

4

Ignore

vbIgnore

5

Yes

vbYes

6

No

vbNo

7

Example 1 - Output Basic Text

Basic message box with text.

This is the simplest form of outputting a message.

MsgBox "Hi, this is my message."

a63ffa7cd8882f6ceadecdb8df1f21ba.jpg

Running the macro we get this:

c46b3081178c9e6626f8785ee388b478.jpg

Remember, the OK button is there by default and clicking that simply closes the pop-up message box window.

Example 2 - Add Buttons to the Message Box

Message box with multiple buttons.

MsgBox "Hi, this is my message.", vbYesNoCancel

db0f8fdeac20a772711bf8eafc93ce33.jpg

I added a comma after the display text and then input one of the VB codes from the Button Arguments list above.

2dea7d7b5e6391765e3a6b6661789ebd.jpg

Run the macro and we get this in Excel:

f8ac44abed8da3166c9ce60897767240.jpg

Example 3 - Figure Out Which Button Was Clicked

When a button is clicked, it sends a value back to Excel. Now, we need to capture that value so we can do something with it.

response = MsgBox("Hi, this is my message.", vbYesNoCancel)

4201571bd7a681c0b0102d55a8ff2ec2.jpg

All we have to do is to set a variable equal to the output of the MsgBox function. Here, the variable response is equal to the output of the function, which means that we type response =  and then the msgbox function.

Note also that there are now parentheses surrounding the arguments for the MsgBox function; these weren't included before because they weren't needed. Often, they aren't used when creating simple pop-up windows.

I will now output the variable response into a Msgbox of its own so you can see the value it returns after a button was clicked.

Here is the final code for this example:

b6056271ee5283726f0493532c267437.jpg

Run it and you get this:

7ee7a2cc2dd7f2214b8abc8f2e9773a9.jpg

Click one of the buttons and the next message box will open with the value that was returned as a result of the button click in the first window.

ad4d29f171f3b99745743da22c860edb.jpg

A 6 is returned, which means the button Yes was clicked. We know this from the table above "Values Returned from Button Clicks in the Message Box", which lists the values that each button click will return.

Example 4 - Do Something After the User Clicks a Button

Now that you know how to determine which button was clicked, let's do something useful with it.

I will make a simple IF statement that checks which button was clicked and outputs a message based on that.

Sub button_action_msgbox()

response = MsgBox("Hi, this is my message.", vbYesNo)

If response = 6 Then
    MsgBox "You clicked Yes"
ElseIf response = 7 Then
    MsgBox "You clicked No"
End If

End Sub

74e8e0e525ee2b884813984e0bb0585f.jpg

Run the macro:

5a3ec2e9d11e8f438483a7b79788801a.jpg

Hit one of the buttons and the next part of the macro will run and the corresponding msgbox will appear:

1a40178ea6aa380be3256decf2a543f8.jpg

At this point, we have created a useful pop-up message box that solicits feedback and does something with that feedback.

Example 5 - Change MsgBox Appearance

You can add some additional things to the pop-up window to change its look and feel and here I will include some of those options, including a custom title for the window.

Sub button_style_msgbox()

MsgBox _
    "Hi, this is my message.", _
    vbOKOnly + vbInformation, _
    "Custom Title"

End Sub

8d756ee11ae6c7f7f5dbd0ba45030a52.jpg

First, notice that I used "_" at the end of each line so that I could place this piece of code on multiple lines.

Second, notice that there is now a custom title called Custom Title that will appear for the msgbox.

Third, notice that there is more than one option for the buttons argument and that each argument is separated with a plus sign (+) like this: vbOKOnly + vbInformation.

Run the macro and we get this:

12471f5ba0f2c25a758a04457f3b1079.jpg

The option vbOKOnly meant there would be only an OK button. The option vbInformation put that image with the i inside the blue circle in there. The custom title appears in the upper-left corner of the pop-up message box window.

Notes

These are a few examples that should get you comfortable using the Message Box pop-up window feature in macros in Excel.

This is a great way to give some information to your user that they must process or at least must reply to in some way before continuing.

Make sure to download the sample file attached to this tutorial to get all of the sample VBA Macro codes.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course