The Pros and Cons of Message Boxes

These are the advantages of using a message box:

• Users can't miss seeing the message box. Users are prevented from continuing to use the application until they close the message box. (If you want, you can even display a message box that the user can't escape by pressing Alt+Tab to switch to another application. You'll look at this a little later in the chapter.)
• You can present the user with a simple choice among two or three options.

These are the disadvantages of using a message box:

• A message box can present only one, two, or three buttons, which means it can offer only a limited set of options to the user.
• The buttons in message boxes are predefined in sets—you can't put a custom button in a message box. (For that, you have to use a dialog box.)
• Within message boxes you can't use user-interface features such as text boxes, group boxes, or list boxes.

Message-Box Syntax

The basic syntax for message boxes is as follows:

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

Here's what the elements of this syntax mean:

• MsgBox The function that VBA uses to display a message box. You typically use it with a number of arguments enclosed in parentheses after it.
• Prompt A required argument for the MsgBox function that specifies what text is displayed in the message box. prompt is a String argument, meaning you need to type in the text of your choice. It can be up to approximately 1024 characters long, although it's usually a good idea to be more concise than this. (Any prompt longer than approximately 1024 characters is truncated to approximately 1024 characters without warning.)
• Buttons An optional argument that controls the type of message box that VBA displays by specifying which buttons it contains. For example, as you'll see in a couple of pages, you can display a message box with just an OK button; with OK and Cancel buttons; with Abort, Retry, and Ignore buttons; and so on. You can also add arguments to the buttons argument that control the icon in the message box and the modality of the message box. You'll also look at these options later in this chapter.
• Title An optional argument that controls the title bar of the message box. This, too, is a String argument. If you don't specify title, VBA uses the application's title—Microsoft Word for Word, Microsoft Excel for Excel, Microsoft PowerPoint for PowerPoint, and so on. Usually, it's best to specify the title because the application name on its own isn't helpful (unless the user has become confused as to which application is running the macro).
• Helpfile An optional argument that controls which Help file VBA displays when the user presses F1 within the message box to get help (or clicks the Help button in a message box that contains a Help button).
• Context An optional argument that controls which topic in the Help file VBA jumps to. If you specify the helpfile argument, you must specify the context argument as well.

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

Displaying a Simple Message Box

You can display the simplest 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.

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

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 macro (by pressing the F5 key, by clicking the Run Sub/UserForm button, or by choosing Run ➢ Run Sub/UserForm).

Instead of entering a literal text string for the prompt argument, you could 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 macro or a string dynamically created by the macro (for example, after having gotten the user's name via an input box).

Displaying a Multiline Message 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 1024 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:

• Chr(13) or vbCr represents a carriage return.
• Chr(10) or vbLf represents a line feed.
• Chr(10) + Chr(13) or vbCrLf represents a line-feed/carriage-return combination.

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

Choosing Buttons for a Message Box

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 specify these message-box types in your code 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)

From VBA's point of view, it doesn't matter whether you use values or constants in the message boxes for your macros. For the human, though, the text constants are far more 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.

Choosing an Icon for a Message Box

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)

Setting a Default Button for a Message Box

As usual in the Windows interface, the user is cued to a default button in a message box. It's the one with a blue 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, thereby displaying a fourth button. 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 make a different button the default, 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)

Controlling the Modality of 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 entire computer until you dismiss them.

Most message boxes are application modal, allowing users to switch to another application by pressing Alt+Tab (or switching via the Taskbar). Users can then work in the other application even though they haven't gotten rid of the message box. This gives them freedom and flexibility. In contrast, some message boxes (most often used during an installation process) 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 you're designing macros, use system-modal message boxes only when absolutely necessary—for example, when an action might result in data loss or system instability. Most of the time application-modal message boxes will do everything you need them to—and won't confuse or vex your users.

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 does stay “on top,” remaining displayed—enough to annoy users but not totally 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.

Specifying a Title for a Message Box

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

The string expression title can be up to 1024 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.

Title Bars Can Provide Useful Information

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 macro in the title bar of a message box and then use the prompt argument to explain what actions the buttons in the message box will trigger.

In addition, if you expect to revise your macros, you may find it helpful to include their version number in the title so that users can easily check which version of the macro they're using (and update to a more current version as appropriate). For instance, the Delete Workbook macro is identified as version 12.39 in the message box shown in Figure 13.8.

Specify the title argument after the buttons argument like this:

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

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 macro calls. Or you might need to display in the title of the message box a string created or stored in the macro.

Adding a Help Button to a Message Box

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 if the user clicks it. You must also specify which Help file and topic you want shown (see the next section for details). Figure 13.9 shows the message box that this statement produces.

Specifying a Help File for a Message Box

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

• The helpfile argument is a string argument specifying the name and location of the Help file that VBA displays when the user summons help from the message box.
• The context argument is a Help context number within the Help file. The Help context number controls which Help-file topic is displayed.

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 official 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 choices 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 number. Users must then locate the information they need on their own.

Using Some Arguments Without Others

Recall that optional arguments are just that, optional. So, 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 in VBA.) 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:WindowsHelpMacro 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 code easier to read:

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

Retrieving a Value from a Message Box

If you display a vbOKOnly message box, your code will know which button the user clicks because the message box contains only that single OK button. But when you use one of the other message-box styles, which can have two, three, or four buttons, how does your code know which button was clicked?

To do that, you must retrieve a value from the message box code that tells you which button the user clicked. (You can then branch execution to respond appropriately to the user's choice.)

To retrieve a value from a message box, declare a variable for it (just as we've been doing throughout this chapter). You can do so quite simply by telling VBA to “assign” the message box to the variable, 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 name or the value number. 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 macro from there. If not, it terminates the macro 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.

Input-Box Syntax

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:

• Prompt A required string that specifies the prompt that appears in the input box. As with MsgBox, prompt can be up to about 1024 characters long, and you can use the carriage-return constant (vbCr) to force separate lines. Like the MsgBox prompt argument, the InputBox prompt automatically wraps if the prompt is longer than about 35 characters.
• Title A string that specifies the text in the title bar of the input box. If you don't specify a title argument, VBA supplies the application's name.
• Default A string that you can use to specify text that will appear in the text box. Entering a default argument can be a good idea both for cases when the default text is likely to be suitable (so the user can just press Enter to accept and transmit that default) or when you need to display sample text so that the user can see what type of response you're looking for.

  Here's an example of suitable default text to cue the user: If you display an input box asking for the user's name, you could enter the Name value by fetching it from the BuiltInDocumentProperties collection of the ActiveDocument object, like this:

   Dim strAuthor As String
     strAuthor = _
     ActiveDocument.BuiltInDocumentProperties(wdPropertyLastAuthor)

• xpos and ypos These are optional numeric values for specifying the onscreen position of the input box. The xpos value governs the horizontal position of the left edge of the input box from the left edge of the screen (not of the Word window), whereas ypos governs the vertical position of the top edge of the input box from the top of the screen. Each measurement is in twips, described in the sidebar “Input Boxes Are Usually Best Displayed in the Center of the Screen” in this chapter. If you omit these two arguments, VBA displays the input box at the default position of halfway across the screen and one-third of the way down it.
• helpfile and context Optional arguments for specifying the Help file and context in the Help file to jump to if the user summons help from the input box. If you use helpfile, you must also use context.

You can omit any of the optional arguments for an input box. But if you want to use another argument 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)

Retrieving Input from an Input Box

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