The Msg Box Function

You're probably already familiar with the VBA MsgBox function — I use it quite a bit in the examples throughout this book. The MsgBox function, which accepts the arguments shown in Table 15-1, is handy for displaying information and getting simple user input.

Here's a simplified version of the syntax for the MsgBox function:

MsgBox(prompt[,

buttons][, title])

Table 15-1

MsgBox Function Arguments

Argument

What It Does

prompt

Supplies the text Excel displays in the message box

buttons

Specifies which buttons (and what icon) appear in the message box (optional)

title

Defines the text that appears in the message box's title bar (optional)

Displaying a simple message box

You can use the MsgBox function by itself or assign its result (that is, the button clicked by the user) to a variable. If you use this function by itself, don't include parentheses around the arguments. The following example simply displays a message and does not return a result:

Sub MsgBoxDemo()

MsgBox "Click OK to begin printing." End Sub

Figure 15-1 shows how this message box looks.

Figure 15-1:

A simple message box.

Microsoft Excel m

Click OK to continue.

Getting a response from a message box

If you display a message box that has more than just an OK button, you'll probably want to know which button the user clicks. Fortunately, the MsgBox function returns a value that represents which button is clicked. You can assign the result of the MsgBox function to a variable. In the following code, I use some built-in constants (which I describe later in Table 15-2) that make it easy to work with the values returned by MsgBox:

Sub

GetAnswer()

Dim Ans As Integer

Ans = MsgBox("Continue?", vbYesNo)

Select Case Ans

Case vbYes

...[code if Ans is Yes]...

Case vbNo

...[code if Ans is No]...

End Select

End

Sub

When you execute this procedure, the Ans variable is assigned a value of either vbYes or vbNo, depending on which button the user clicks. The Select Case statement uses the Ans value to determine which action the routine should perform.

You can also use the MsgBox function result without using a variable, as the following example demonstrates:

Sub

GetAnswer2()

If MsgBox("Continue?",

vbYesNo) = vbYes Then

...[code if Yes is

clicked]...

Else

...[code if Yes is

not clicked]...

End If

End

Sub

Custom/zing message boxes

The flexibility of the buttons argument makes it easy to customize your message boxes. You can specify which buttons to display, determine whether an icon appears, and decide which button is the default. Table 15-2 lists some of the built-in constants you can use for the buttons argument. If you prefer, you can use the value rather than a constant (but I think using the built-in constants is a lot easier).

Table 15-2

Constants Used in the MsgBox Function

Constant

Value

What It Does

vbOKOnly

0

Displays OK button only

vbOKCancel

1

Displays OK and Cancel buttons

vbAbortRetryIgnore

2

Displays Abort, Retry, and Ignore buttons

vbYesNoCancel

3

Displays Yes, No, and Cancel buttons

vbYesNo

4

Displays Yes and No buttons

vbRetryCancel

5

Displays Retry and Cancel buttons

vbCritical

16

Displays Critical Message icon

vbQuestion

32

Displays Warning Query icon

vbExclamation

48

Displays Warning Message icon

vbInformation

64

Displays Information Message icon

vbDefaultButton1

0

First button is default

vbDefaultButton2

256

Second button is default

vbDefaultButton3

512

Third button is default

vbDefaultButton4

768

Fourth button is default

To use more than one of these constants as an argument, just connect them with a + operator. For example, to display a message box with Yes and No buttons and an exclamation icon, use the following expression as the second MsgBox argument:

vbYesNo + vbExclamation

Or, if you like to make your code less understandable, use a value of 52 (that is, 4 + 48).

The following example uses a combination of constants to display a message box with a Yes button and a No button (vbYesNo) as well as a question mark icon (vbQuestion). The constant vbDefaultButton2 designates the second button (No) as the default button — that is, the button that is clicked if the user presses Enter. For simplicity, I assign these constants to the Config variable and then use Config as the second argument in the MsgBox function:

Sub GetAnswer3()

Dim Config As Integer Dim Ans As Integer

Config = vbYesNo + vbQuestion + vbDefaultButton2 Ans = MsgBox("Process the monthly report?", Config) If Ans = vbYes Then RunReport End Sub

Figure 15-2 shows the message box Excel displays when you execute the GetAnswer3 procedure. If the user clicks the Yes button, the routine executes the procedure named RunReport (which is not shown). If the user clicks the No button (or presses Enter), the routine ends with no action. Because I omitted the title argument in the MsgBox function, Excel uses the default title, Microsoft Excel.

Figure 15-2:

The MsgBox function's buttons argument determines what appears in the message box.

Figure 15-2:

The MsgBox function's buttons argument determines what appears in the message box.

The following routine provides another example of using the MsgBox function:

Sub GetAnswer4()

Dim Msg As String, Title As String

Dim Config As Integer, Ans As Integer

Msg = "Do you want to process the monthly report?"

Msg = Msg & vbNewLine & vbNewLine

Msg = Msg & "Processing the monthly report will " Msg = Msg & "take approximately 15 minutes. It " Msg = Msg & "will generate a 30-page report for " Msg = Msg & "all sales offices for the current " Msg = Msg & "month." Title = "XYZ Marketing Company" Config = vbYesNo + vbQuestion Ans = MsgBox(Msg, Config, Title) If Ans = vbYes Then RunReport End Sub

This example demonstrates an efficient way to specify a longer message in a message box. I use a variable (Msg) and the concatenation operator (&) to build the message in a series of statements. The vbNewLine constant inserts a line break character that starts a new line. I also use the title argument to display a different title in the message box. Figure 15-3 shows the message box Excel displays when you execute this procedure.

Figure 15-3:

The dialog box displayed by the MsgBox Function.

Previous examples have used constants (such as vbYes and vbNo) for the return value of a MsgBox function. Besides these two constants, Table 15-3 lists a few others.

Table 15-3 Constants Used as Return Values for the MsgBox Function

Table 15-3 Constants Used as Return Values for the MsgBox Function

Constant

Value

What It Means

vbOK

1

User clicked OK.

vbCancel

2

User clicked Cancel.

vbAbort

3

User clicked Abort.

vbRetry

4

User clicked Retry.

Constant

Value

What It Means

vbIgnore

5

User clicked Ignore.

vbYes

6

User clicked Yes.

vbNo

7

User clicked No.

0 0

Responses

Post a comment