Excel 2010 Bible

Chapter 41: Creating UserForms

841

Constant Value Description

vbRetryCancel 5 Displays Retry and Cancel buttons.

vbCritical 16 Displays Critical Message icon.

vbQuestion 32 Displays Query icon (a question mark).

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.

The following example uses a combination of constants to display a message box with a Yes button

and a No button (vbYesNo), and a question mark icon (vbQuestion). The second button (the

No button) is designated as the default button (vbDefaultButton2), which is the button that is

executed if the user presses Enter. For simplicity, these constants are assigned to the Config vari-

able, and Config is then used as the second argument in the MsgBox function.

Sub GetAnswer()

Config = vbYesNo + vbQuestion + vbDefaultButton2

Ans = MsgBox(“Process the monthly report?”, Config)

If Ans = vbYes Then RunReport

If Ans = vbNo Then Exit Sub

End Sub

Figure 41.4 shows how this message box appears when the GetAnswer Sub is executed. 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 procedure is ended with no action.

Because the title argument was omitted in the MsgBox function, Excel uses the default title

(Microsoft Excel).

FIGURE 41.4

The second argument of the MsgBox function determines what appears in the message box.