These are the advantages of using a message box:

These are the disadvantages of using a message box:

The basic syntax for message boxes is as follows:

MsgBox(prompt[, buttons] [, title][, helpfile, context])

Here's what the elements of this syntax mean:

In the following sections, you'll first look at how you can build the simplest of message boxes and then explore how you can add the other arguments to it to make it more complex.

You can display a straightforward message box by specifying only the prompt as a text string enclosed in double quotation marks:

MsgBox "This is a simple message box."

Run from Excel, this statement produces the simple message box shown in Figure 13.2. With prompt as the only argument supplied, VBA produces a message box with only an OK button and with the application's name in the title bar. This message box does nothing except display information.

Figure 13.2. When you use only the prompt argument to display a simple message box, VBA uses the application's name as the title.

You can enter this MsgBox statement on any blank line within a procedure. After you type the MsgBox keyword, VBA's Auto List Members feature prompts you with the syntax of the function, as shown in Figure 13.3.

Figure 13.3. VBA's Auto List Members feature prompts you with the syntax for the message box.

Once you've entered the MsgBox statement with its required argument (prompt), you can display the message box by stepping through the code (by pressing the F8 key or clicking the Step Into button on the editor's Debug toolbar) or by running the procedure (by pressing the F5 key, or by clicking the Run Sub/UserForm button, or by choosing Run

Instead of entering a literal text string for the prompt argument, you can use a String variable. The following example uses a String variable named strMsg:

Dim strMsg As String
strMsg = "This is a simple message box."
MsgBox strMsg

This approach can be useful when you're working with long strings (you can build a big string by concatenating several shorter strings with the & operator). Using a variable is also useful when you need to display a string that has been defined earlier in the procedure or a string dynamically created by the procedure (for example, after having gotten the user's name via an input box).

By default, VBA displays short message strings as a single line in a message box and wraps longer strings onto two or more lines as necessary, up to the limit of 1,024 characters in a string.

You can deliberately break a string into more than one line by including line-feed and carriage-return characters in the string as follows:

In message boxes, these three characters all have the same effect—moving down one line. Your code is easier to read if you use a built-in constant (vbCr, vbLf, or vbCrLf) rather than the corresponding Chr() construction; it's also quicker to type. Usually, it's clearest to use the vbCr constant.

You can add a tab to a string by using Chr(9) or vbTab. Again, vbTab is easier to read and to type.

The following code displays the Word message box shown in Figure 13.4. Note that each part of the text string is enclosed in double quotation marks (to tell VBA that they're part of the string). The Chr(149) characters are bullets, so the text after them starts with a couple of spaces to give the bullets some room:

Dim strMsg As String
strMsg = "Word has finished formatting the report you requested." _
    & vbCr & vbCr & "You can now run the following procedures:" & vbCr _
    & vbCr & Chr(149) & " Distribute_Report will email the report to " _
    & "the head office." & vbCr & vbCr & Chr(149) & _
    " Store_Report will copy the report to the holding directory." _
    & vbCr & vbCr & Chr(149) & " Backup_Report will create a backup " _
    & "of the report on the file server."
MsgBox strMsg

Figure 13.4. You can display a multiline message box by using line-feed and carriage-return characters within the prompt string.

The buttons argument controls which buttons a message box contains. VBA offers the types of message boxes shown in Table 13.1, controlled by the buttons argument.

You can refer to these message box types by using either the numeric value or the constant. For example, you can specify either 1 or vbOKCancel to produce a message box with OK and Cancel buttons. The value is easier to type; the constant is easier to read. Either of the following statements produces the message box shown in Figure 13.5 when run from PowerPoint:

Dim lngR As Long
lngR = MsgBox("Apply standard formatting to the slide?", vbYesNo)
lngR = MsgBox("Apply standard formatting to the slide?", 4)

Figure 13.5. The vbYesNo constant produces a message box with Yes and No buttons.

From VBA's point of view, it doesn't matter whether you use values or constants in the message boxes for your procedures. For the human, though, the text constants are far preferable. Even if you're the only person who ever sees your code, the code is much easier to read if you use the constants.

You can also add an icon to a message box by including the appropriate value or constant argument. Table 13.2 shows the options.

Again, you can refer to these icons by using either the value or the constant: Either 48 or vbExclamation will produce an exclamation point icon. Again, the constant is much easier to read.

To link the value or constant for the message box with the value or constant for the icon, use a plus sign (+). For example, to produce a message box containing Yes and No buttons together with a question mark icon (see Figure 13.6), you could enter vbYesNo + vbQuestion (or 4 + 32, vbYesNo + 32, or 4 + vbQuestion):

lngR = MsgBox("Apply standard formatting to the slide?", _
    vbYesNo + vbQuestion)

Figure 13.6. Adding an icon gives a message box greater visual impact.

As usual in the Windows interface, the user is cued to a default button in a message box. It's the one with a black border around its outside and a dotted line around its text area. (See the Yes button in Figure 13.6.) The user can move the selection to another button by using Tab or Shift+Tab or the →, ←, ↑, or ↓key.

However, you can specify in your code which button you want to be the default.

Table 13.3 lists the arguments for default buttons.

All the message boxes mentioned so far have only one, two, or three buttons, but you can add a Help button to any of the message boxes, making for a fourth button on those boxes that already have three buttons (such as vbYesNoCancel). You'll see how to add the Help button in the section "Adding a Help Button to a Message Box" later in this chapter.

In VBA, unless you specify otherwise, the first button on each of the message boxes is automatically the default button: for example, the OK button in a vbOKCancel message box, the Abort button in a vbAbortRetryIgnore message box, the Yes button in a vbYesNoCancel message box, the Yes button in a vbYesNo message box, and the Retry button in a vbRetryCancel message box. VBA counts the buttons in the order they're presented in the constant for the type of message box (which in turn is the left-to-right order in which they appear in the message box onscreen). So in a vbYesNoCancel message box, Yes is the first button, No is the second button, and Cancel is the third button.

To switch to a different default button, specify the value or constant as part of the buttons argument. When run in PowerPoint, this statement produces the message box shown in Figure 13.7.

Dim lngQuery As Long
lngQuery = MsgBox("Do you want to delete this presentation?", _
    vbYesNo + vbCritical + vbDefaultButton2)

Figure 13.7. Specify a default button to steer the user toward a particular button in a message box.

VBA can display both application-modal message boxes and system-modal message boxes—at least in theory. Application-modal message boxes stop you from doing anything in the current application until you dismiss them, whereas system-modal message boxes stop you from doing anything on your computer until you dismiss them.

Most message boxes are application modal, allowing the user to "coolswitch" by pressing Alt+Tab (or switch via the Taskbar) to another application and work in it before you get rid of the message box, which gives you a reasonable amount of flexibility. In contrast, some installation message boxes are system modal, insisting that users concentrate their attention on them and them alone. Windows's critical system errors and "you must restart your computer now" messages are system modal to prevent you from avoiding them.

You probably know from your own experience how frustrating system-modal message boxes can be. So when designing procedures, use system-modal message boxes only when absolutely necessary—for example, when an action might result in data loss or system instability. For most conventional purposes, application-modal message boxes will do everything you need them to—and won't confuse or vex users of your procedures.

In theory, you can control the modality of a message box by using the two buttons arguments shown in Table 13.4.

In practice, even if you use the vbSystemModal argument, the user can switch to another application (provided that one is running) and continue working. However, the message box stays "on top," remaining displayed—enough to annoy users but not to prevent them from accessing another application.

By default, message boxes are application modal, so you need to specify modality only on those rare occasions when you need a system-modal message box. When you do, add the vbSystemModal constant or 4096 value to the buttons argument:

Response = MsgBox("Do you want to delete this document?", _
    vbYesNo + vbCritical + vbDefaultButton2 + vbSystemModal)

Please note that system-modal message boxes look the same as application-modal message boxes.

The next component of the message box is its title bar, which is controlled by the optional title argument. If you omit title, VBA supplies the application's name as the title, but users of your procedures will benefit from your providing a more helpful title.

title is a string expression and can be up to 1,024 characters in length, in theory (longer strings are truncated with no warning or error message), but in practice, any title longer than about 75 characters gets truncated with an ellipsis. If you want people to read the title bars of your message boxes, 25 characters or so is a reasonable maximum.

The title bar is usually the first part of a message box that the user notices, so make your title bars as helpful as possible. Conventional etiquette is to put the name of the procedure in the title bar of a message box and then use the prompt to explain what choices the buttons in the message box will implement. In addition, if you expect to revise your procedures, you may find it helpful to include their version number in the title so that users can easily check which version of the procedure they're using (and update to a more current version as appropriate). For instance, the Delete Workbook procedure is identified as version 12.39 in the message box shown in Figure 13.8.

Figure 13.8. Usually, you'll want to specify the title argument for your message boxes. You may also want to include a version number.

Specify the title argument after the buttons argument like this:

lngQuery = MsgBox("Do you want to delete this workbook?", vbYesNo _
    + vbCritical + vbDefaultButton2, "Delete Workbook 12.39")

As with the prompt argument, you can use a string variable as the title argument. For example, you could declare a single string variable and use it to supply the title for each message box that a procedure calls. Or you might need to display in the title of the message box a string created or stored in the procedure.

To add a Help button to a message box, use the vbMsgBoxHelpButton constant. You add this argument to whichever buttons you're specifying for the message box:

lngQuery = MsgBox("Do you want to delete this workbook?", vbYesNo _
    + vbCritical + vbDefaultButton2 + vbMsgBoxHelpButton, _
    "Delete Workbook")

Adding the vbMsgBoxHelpButton argument simply places the Help button in the message box—it doesn't make the Help button display a Help file until you specify which Help file and topic it should use (see the next section for details). Figure 13.9 shows the message box that this statement produces.

Figure 13.9. Use the vbMsgBoxHelpButton constant to add a Help button to a message box.

The final arguments you can use for a message box are the helpfile and context arguments:

The helpfile and context arguments are primarily useful if you're writing your own Help files because otherwise it's difficult to access the Help context numbers, which are buried in the Help files. If you're writing your own Help files, the syntax for specifying the helpfile and context arguments is simple:

Dim lngQuery As Long
lngQuery = MsgBox("Do you want to delete this workbook?", vbYesNo _
    + vbCritical + vbDefaultButton2 + vbMsgBoxHelpButton, _
    "Delete Workbook", "c:WindowsHelpMy_Help.chm", 1012)

In this case, the Help file is specified as My_Help.chm in the WindowsHelp folder. VBA displays the Help topic numbered 1012.

When the user clicks the Help button in the message box, VBA displays the specified topic in the Help file. The message box stays onscreen so that when users have finished consulting the Help file, they can make their choice in the message box.

The Help context number for the opening screen of a Help file is 0. Use 0 when you need to display a Help file for which you don't know the Help context numbers. Users must then find the information they need on their own.

When displaying a message box, you can either specify or omit optional arguments. If you want to specify arguments later in the argument list without specifying the ones before them, use a comma to indicate each unused optional argument. (This technique can be used with any argument list.) For example, if you wanted to display the message box shown in the previous example without specifying buttons and title arguments, you could use the following statement:

Response = MsgBox("Do you want to format the report?",,, _
    "c:WindowsHelpProcedure Help.chm", 1012

Here, the triple comma indicates that the buttons and title arguments are omitted (which will cause VBA to display defaults—a vbOKOnly message box with a title bar containing the application's name), preventing VBA from confusing the helpfile argument with the buttons argument. Alternatively, you could use named arguments, which makes for less-concise but easier-to-read code:

Response = MsgBox("Do you want to format the report?", _
    HelpFile:="c:WindowsHelpProcedure Help.chm", Context:= 1012)

If you display a vbOKOnly message box, you know which button the user clicks because the message box contains only an OK button. But when you use one of the other message box styles, which can have two, three, or four buttons, you must retrieve a value that tells you which button the user clicked. You can then point the procedure in the appropriate direction—to the appropriate code.

To retrieve a value from a message box, declare a variable for it. You can do so quite simply by telling VBA that the variable name is equal to the message box (so to speak), like this:

Dim lngResponse As Long
lngResponse = MsgBox("Do you want to create the daily report?", _
    vbYesNo + vbQuestion, "Create Daily Report")

You first declare a variable of the appropriate type (a Long variable) to contain the user's choice, as in the examples throughout this chapter:

When you run the code, VBA stores which button the user clicked as a value in the variable. You can then check the value and take action accordingly. Table 13.5 shows the full list of buttons the user may choose. You can refer to the buttons by either the constant or the value. As usual, the constant is easier to read than the value.

For example, to check a vbYesNo message box to see which button the user chose, you can use a straightforward If... Then... Else statement:

Dim lngUserChoice As Long
lngUserChoice = MsgBox("Do you want to create the daily report?", _
    vbYesNo + vbQuestion, "Create Daily Report")
If lngUserChoice = vbYes Then
    Goto CreateDailyReport
Else
    Goto Bye
EndIf

Here, if the user chooses the Yes button, VBA goes to the line of code identified by the CreateDailyReport label and continues running the procedure from there; if not, it terminates the procedure by going to the Bye label at the end. The If condition checks the response generated by the choice the user made in the message box to see if it's a vbYes (generated by clicking the Yes button or pressing Enter with the Yes button selected). The Else statement runs if the response was not vbYes—that is, if the user clicked the No button or pressed Esc.

The syntax for displaying an input box is straightforward and similar to the syntax for a message box:

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

Here's what the arguments mean:

You can omit any of the optional arguments for an input box. But if you want to use an optional argument other than one you've omitted later in the syntax sequence, remember that you need to indicate the omission with a spacer comma (or use named arguments as described earlier in this chapter).

Unlike message boxes, input boxes come with a predefined set of buttons—OK and Cancel, plus a Help button if you specify the helpfile and context arguments—so there's no need to specify the main buttons for an input box. The following example declares the String variable strWhichOffice and assigns to it the result of the input box shown in Figure 13.11.

Dim strWhichOffice As String
strWhichOffice = InputBox( _
    "Enter the name of the office that you visited:", _
    "Expense Assistant", "Madrid", , , _
    "c:WindowsHelpProcedure Help.chm", 0)

Figure 13.11. The input box comes with a predefined set of buttons.

To retrieve the user's input from an input box, declare the numeric variable or String variable that will contain it. Here, the variable strWhichOffice will contain what the user types into the input box:

Dim strWhichOffice
strWhichOffice = _
    InputBox("Enter the name of the office that you visited:", _
    "Expense Assistant 2000", "Madrid", , , _
    "c:WindowsHelpProcedure Help.chm", 0)

Once the user has entered a value or a string and clicked the OK button, your code can then use the returned value as usual in VBA. To make sure the user has clicked the OK button, check that the input box hasn't returned a zero-length string (which it also returns if the user chooses the OK button with the text box empty) and take action accordingly:

strWhichOffice = InputBox _
    ("Enter the name of the office that you visited:", _
    "Expense Assistant 2000", "Madrid", , , _
    "c:WindowsHelpProcedure Help.chm", 0)
If strWhichOffice = "" Then End