How to Create a VBA Message Box in Excel (“msgbox”) for Non-Geeks

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

In this article, we are going to show you how to create custom message boxes in Microsoft Excel using VBA.

If you do not have any experience with VBA, do not worry (make sure to check this out too).

This tutorial will be a great introduction for you!

Soon you can impress your friends and colleagues. Furthermore, you may find some cool uses for your new-found message box skills.

Kasper Langmann, Co-founder of Spreadsheeto

What is a VBA message box?

A VBA message box is a pop-up style dialog box that you can program the behavior of using VBA.

It provides an elegant and sometimes necessary way for the end user to interact with a workbook.

It can be something as simple as an alert to the user. It could be something more complex that requires action by the user to proceed.

Getting started with VBA

If you have never used VBA or worked with macros, you have likely never used the ‘Developer Tab‘. For some of you, that may mean it is not even visible.

For those of you who cannot see the Developer tab, go to the File tab and then to Options.

file-menu-options

Select Customize Ribbon from the list of options. At this point, make sure the box for Developer in the Main Tabs list on the right is checked.

main-tabs-developer

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

cloud-download

BONUS: Download the VBA Message Box Exercise Workbook File to go along with this post.

Syntax

Like functions and formulas in Excel, the MSGBOX function is how we create a message box using VBA.

Before we start writing VBA code, let’s take a quick look at the syntax requirements for the MSGBOX function.

The syntax of the MSGBOX function

=MSGBOX(Text_String , [buttons] ,[title], [helpfile, context])

The first and only required argument is Text_String. This is the message text.

It is the text string you want to the message box to say to the user. You must enclose the text string in double quotes and limit it to 1,024 characters.

The buttons argument is optional but can you can use it to make your message box interactive. This argument is not an arbitrary value.

There are 20 available button parameter constants available in Excel.

 

button-parameters

If you omit the button argument, the default is of type vbOkayOnly.

The next argument is the title parameter. It is also optional.

This argument assigns a custom title to the message box. It is a string expression that displays in the title bar of the dialog box.

If omitted, the title bar will display the application name.

The helpfile argument is a string parameter and it is optional. It becomes required if you use the context parameter.

The helpfile argument specifies the help file for use with the message box.

The final argument, context, is a numeric parameter. This argument specifies the number assigned to the appropriate Help topic.

The context argument is an optional parameter unless there is a helpfile argument.

First example: basic concept

Let’s create our first message box. In this example, we just want to create a dialog box to say “Hello world!”

The first thing we need to do is click on Visual Basic in the Code group on the Developer tab.

visual-basic-button

You can also press Alt + F11 to get the same result.

You should now have the VB Editor open. In the Project Explorer , you will find your workbook file listed.

Expand the contents of the workbook and right click on Microsoft Excel Objects. Hover your cursor over Insert and select Module from the menu that appears.

vb-module

This will create a new blank module.

blank-module

Now we can start creating our first message box.

Kasper Langmann, Co-founder of Spreadsheeto

At the top of the code window, type “Sub firstMessage()” (without the quotes). Notice that Excel adds the line “End Sub” below this line when you press Enter.

All the code we will type next must be between these two lines.

first-last-vb

This is where we begin typing our MsgBox function.

msgbox-vba

Note that when we type MsgBox, the syntax help appears.

Kasper Langmann, Co-founder of Spreadsheeto
syntax-help

Now we can run the macro by clicking the Run Sub button (green “play” button) or pressing F5.

run-sub

This will cause our message box to appear.

message-box

Note that the default button is “OK” only.

Also, the title is the default showing the application name (“Microsoft Excel”).

Second example: add a title

Let’s continue to work with the same code by copying and pasting it to a new sub routine.

We will tweak the sub name and add a title to replace the default in our message box.

We have to place two commas between our Text_string argument and title argument. This is because we are omitting the ‘button’ argument.

two-commas

Now we get a message box with our new custom title.

message-box-title

Third example: selecting a different button argument

Now let’s try a different button argument.

Instead of the default vbOKOnly, we will select vbYesNoCancel. In the process, we will also change our Text_string argument to a question for the sake of relevance.

message-question

This code renders the following message box.

yes-no-cancel

Fourth example: multiple lines of text

You can also create multiple lines of text in a message box using the vbNewLine character.

vbnewline

This renders the following message box.

multiple-line-message

Final example: advanced technique using the “if” statement

In our final example, we are going to take the message box concept to the next level.

We will use an “if” statement to generate a different message box.

This relies on a “Yes” response or “No” response for the first message box in the sub routine.

vba-if-code

This code is a bit more complex than what we have been working with.

It triggers a different message box for each of the possible responses to the first message box.

If the user clicks “Yes” the sub routine will open the following message box.

proceed-message-box

If the user click “No” the sub routine will generate the following message box.

sorry-message-box

This example starts to scratch the surface of how you can use a message box to direct the flow of your macros.

Kasper Langmann, Co-founder of Spreadsheeto

This can get much more complex if you decide to continue your VBA and message box skill development.

Conclusion

The VBA message box is not just an awesome programming trick.

Yes, you can use them to impress your friends and colleagues.

But it is also a very useful tool for creating interactivity and control for your workbooks.