Excel 2010 Bible

Part VI: Programming Excel with VBA

840

FIGURE 41.3

A simple message box, displayed with the VBA MsgBox function.

To get a response from a message box, you can assign the result of the MsgBox function to a vari-

able. The following code uses some built-in constants (described in Table 41.1) to make it easier to

work with the values that are returned by MsgBox:

Sub GetAnswer()

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 this procedure is executed, the Ans variable contains a value that corresponds to vbYes or

vbNo. The Select Case statement determines the action to take based on the value of Ans.

You can easily customize your message boxes because of the flexibility of the buttons argument.

Table 41.1 lists the built-in constants that you can use for the buttons argument. You can specify

which buttons to display, whether an icon appears, and which button is the default.

TABLE 41.1

Constants Used in the MsgBox Function

Constant Value Description

vbOKOnly 0 Displays OK button.

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.