• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / VBA / Excel VBA MsgBox: Complete Guide with Examples

Excel VBA MsgBox: Complete Guide with Examples

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.

Excel VBA MsgBox to ask the user a question

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"
Simple message box in VBA

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”
Excel VBA Message box with a variable for information

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 TypeDescription
vbOKOnlyDisplay OK button only
vbOKCancelDisplay OK and Cancel buttons
vbAbortRetryIgnoreDisplay Abort, Retry, and Ignore buttons
vbYesNoCancelDisplay Yes, No, and Cancel buttons
vbYesNoDisplay Yes and No buttons
vbRetryCancelDisplay 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?"
Yes/No question on a message box in Excel

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.

ConstantValue
vbOK1
vbCancel2
vbAbort3
vbRetry4
vbIgnore5
vbYes6
vbNo7

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.

ConstantDescription
vbCriticalDisplay Critical Message icon.
vbQuestionDisplay Warning Query icon.
vbExclamationDisplay Warning Message icon.
vbInformationDisplay 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?"
Adding an icon to your Excel VBA MsgBox

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.

ConstantDescription
vbDefaultButton1First button is default
vbDefaultButton2Second button is default
vbDefaultButton3Third button is default
vbDefaultButton4Fourth 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.

ConstantDescription
vbApplicationModalApplication modal; the user must respond to the message box before continuing work in the current application
vbSystemModalSystem 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.

Adding a help button to the message

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.

Related Posts:

  • Copy a formula down in Excel
    How to Copy a Formula Down in Excel
  • Live currency exchange rate in Excel thumbnail
    LIVE Currency Exchange Rate in Excel
  • N Functions in Excel thumbnail
    N Function in Excel
  • IMAGE function in Excel
    Excel IMAGE Function - Insert Images from a Cell Value

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Recent Posts

  • Excel SWITCH Function – Nested IF Function Alternative
  • Sum Formulas Only in Excel
  • How to Calculate Age in Excel
  • Custom Sort in Excel
  • Case Sensitive Lookup with XLOOKUP in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

LinkedIn Twitter YouTube

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Blog
  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2009 Computergaga · All Rights Reserved ·