Using message boxes in Excel VBA is an essential skill for a budding VBA learner. By using the Excel VBA MsgBox function, you can communicate with the user by displaying messages to them, ask them questions and capture their responses.
In fact, the Excel VBA MsgBox function (message box) is probably the most popular function in Excel VBA, and one of the first you learn.
It quickly enables VBA beginners to be able to interact with the user of the macro.
This blog post will explain how to use the MsgBox function and show a few examples of it in action.
The Syntax of the Excel VBA MsgBox Function
=MsgBox(prompt, [Buttons], [Title], [HelpFile], [Context])
Prompt: This is the message text you want to display to the user. This is the only required argument.
Buttons: With this argument you can specify what buttons, icon, defaults and the modality of the message box. If not used, the default button type is VbOkOnly.
Title: Here you can assign a custom title to the Title bar. If you omit it, the default response is to assign the name “Microsoft Excel”.
HelpFile: This is used to specify the location of a help file for the user. It is a string argument and if used, the context argument becomes mandatory. A Help button would need to be added to the message box to access this file (discussed later in this tutorial).
Context: A number that specifies which help topic from the HelpFile to display.
Create a Simple Message to Provide Information
Let’s have a look at a simple example to warm us up.
The MsgBox function is often used to create quick and simple messages to provide information for users.
The example below shows a simple confirmation message to inform the user that the data was successfully archived.
MsgBox "The data has been successfully archived"
Add Useful Information from the Macro to the Message
The previous message can be taken further by including data from a worksheet, or a variable.
Imagine a macro that deletes all the blank rows on a worksheet. And a variable named “NoDeleted” is used to record how many rows were deleted in total.
In the example below, this variable has been included in the message. The ampersand character has been used to join the first and last parts of the message either side of the variable.
The Title argument has also been completed this time. Two commas have been entered to skip the optional Buttons argument.
MsgBox "You have deleted " & NoDeleted & " rows.", ,”Number Deleted”
Writing Multiple Lines of Text
You may want to show your message on multiple lines.
Well fortunately this is easy to do by entering vbCrLf when you would like a new line to begin.
This stands for carriage return line feed. But you do not need to know this.
In the example below it has been added to the message to display the text “Good work” on a new line.
MsgBox "You have deleted " & NoDeleted & " rows." & vbCrLf & "Good work.", , "Number Deleted"
Add Buttons to Interact with Users
Now the best thing about the MsgBox function is the fact that it provides a simple way for us to interact with a user.
By adding buttons to the message box, a user can be consulted on the next step. They can click the appropriate button. This response can then be tested in the macro, and the correct path taken.
The table below shows the button choices available with the built-in MsgBox function.
| Button Type | Description |
| vbOKOnly | Display OK button only |
| vbOKCancel | Display OK and Cancel buttons |
| vbAbortRetryIgnore | Display Abort, Retry, and Ignore buttons |
| vbYesNoCancel | Display Yes, No, and Cancel buttons |
| vbYesNo | Display Yes and No buttons |
| vbRetryCancel | Display Retry and Cancel buttons |
In this example, we have a macro that deletes old data from a spreadsheet that is no longer needed.
In the code below, the vbYesNo buttons have been added to the message box. This enables us to ask the user if they are sure, before we perform the deletion.
MsgBox "Are you sure you want to delete records over 500 days old?", vbYesNo, "Are you sure?"
This message is great. But we need to capture the user response, test it, and take action.
The code below uses an If statement to exit the macro, if the user clicks the No button.
If MsgBox("Are you sure you want to delete records over 500 days old?", vbYesNo, "Are you sure?") = vbNo Then
Exit Sub
End If
The brackets are needed to identify the MsgBox function this time. You may have noticed that they were omitted in the previous examples as it was a statement on its own (not part of a structure like If)
The table below shows the seven different buttons available, and their respective index value.
| Constant | Value |
| vbOK | 1 |
| vbCancel | 2 |
| vbAbort | 3 |
| vbRetry | 4 |
| vbIgnore | 5 |
| vbYes | 6 |
| vbNo | 7 |
It is easy to reference the button choice with VBA as you can use its constant instead of the value. However, if you wanted to assign the response to a variable, that variable should be declared as a Byte data type.
Add Icons to the Message Box in Excel VBA
In addition to adding buttons, you may also want to add an icon. There are four to choose from and they are listed in the table below.
| Constant | Description |
| vbCritical | Display Critical Message icon. |
| vbQuestion | Display Warning Query icon. |
| vbExclamation | Display Warning Message icon. |
| vbInformation | Display Information Message icon. |
The following code shows the vbQuestion icon being added to the previous message.
MsgBox "Are you sure you want to delete records over 500 days old?", vbYesNo + vbQuestion, "Are you sure?"
Specify the Default Button for your Message
You can also specify which button should be the default. A maximum of four buttons can be shown, so your choice is button 1, 2, 3 or 4.
| Constant | Description |
| vbDefaultButton1 | First button is default |
| vbDefaultButton2 | Second button is default |
| vbDefaultButton3 | Third button is default |
| vbDefaultButton4 | Fourth button is default |
In the following VBA, button 2 is specified as the default.
MsgBox "Are you sure you want to delete records over 500 days old?", vbYesNo + vbQuestion + vbDefaultButton2, "Are you sure?"
Setting the Modality of the Excel VBA MsgBox
By setting the modality of a message box, you can prevent the user from interacting with other software whilst the message is on screen.
You have two modality options.
| Constant | Description |
| vbApplicationModal | Application modal; the user must respond to the message box before continuing work in the current application |
| vbSystemModal | System modal; all applications are suspended until the user responds to the message box |
In the example below, the vbSystemModal setting is applied.
MsgBox "Are you sure you want to delete records over 500 days old?", vbYesNo + vbQuestion + vbSystemModal, "Are you sure?"
Add a Help Button and File
The last 2 arguments of the MsgBox function go together. They are used to specify the path to a help file you have created, and a context number.
If you use these, you will need to add the Help button to the message box so that the user can access the help file.
The VBA code below shows an example of how this can be done.
MsgBox "Are you sure you want to delete records over 500 days old?", vbYesNo + vbMsgBoxHelpButton, "Are you sure?", "C:\MessageHelp.chm", 102
For information on how to create a help file, please read this post by Ken Puls.
Final Words
The MsgBox is a built-in pop up box that we can use to communicate with users (a very important requirement when writing macros). They are easy to use, as the function is provided for us.
There are various buttons and settings to adapt the MsgBox to work the way we need them to.
By using UserForms we can have ultimate control over the design (change colours, use logos) of the message/pop up box. We are also not limited to the list of buttons that the MsgBox function provides.
Therefore, learning how to create and work with UserForms is a logical next step in your Excel VBA development, so that you can get more from your messages boxes.
Leave a Reply