MS-Excel / General Formatting

---

Getting Return Values from the Message Dialog Box

A message dialog box that displays only an OK button is straightforward. The user either clicks OK or presses Enter to remove the dialog from the screen. The multibutton styles are a little different, however; the user has a choice of buttons to select, and your procedure should have a way to find out which button the user chose.

You do this by storing the MsgBox function's return value in a variable. Table 3.3 lists the seven possible return values.

You do this by storing the MsgBox function's return value in a variable. Table below lists the seven possible return values.

The MsgBox Function's Return Values

Constant	Value	Button Selected
vbOK	1	OK
vbCancel	2	Cancel
vbAbort	3	Abort
vbRetry	4	Retry
vbIgnore	5	Ignore
vbYes	6	Yes
vbNo	7	No

To process the return value, you test the value in the variable and have your procedure take appropriate action. Listing below shows a revised version of ButtonTest that uses an If statement to see whether the msgResult value equals vbYes. If so, it means the user clicked Yes in the dialog box, so the procedure runs the StoreWorksheetNames procedure; otherwise, it does nothing.

Listing - A Procedure that Handles the Return Value of the MsgBox Function

Sub ButtonTest2()

    Dim msgPrompt As String, msgTitle As String
    Dim msgButtons As Integer, msgResult As Integer

    msgPrompt = "Are you sure you want to display " & vbCrLf & _
                "the worksheet names?"
    msgButtons = vbYesNo + vbQuestion + vbDefaultButton2
    msgTitle = "Display Worksheet Names"

    msgResult = MsgBox(msgPrompt, msgButtons, msgTitle)

    If msgResult = vbYes Then
        StoreWorksheetNames
    End If
End Sub