Note See Chapter for more on running a macro

■ The Excel worksheet function returns the appropriate results.

USING THE MSGBOX FUNCTION

pou can use the MsgBox function to display pop-up message boxes when a VBA procedure executes. The MsgBox function does two things: It displays a dialog box to provide information to the user and it returns a value that indicates the response from the user. You capture the user response by assigning the results of the MsgBox function to a variable. For example, the code UserResponse = MsgBox("Do you want to continue?" ) assigns a value between 1 and 7 indicating the user response to the UserResponse variable. For example, if the user clicks OK, the MsgBox function returns a constant value of vbOK or 1.

The MsgBox function has five different arguments: Prompt,

Buttons, Title, Helpfile, and Context. All but the first argument are optional.

The Prompt argument indicates the value that displays in the message box. You can make this argument a text string and enclose it in quotes or use a variable. You can combine values by using the concatenation operator (&), as in the example: MsgBox( "Total Sum: " & TotalSum)

The optional Buttons argument enables you to specify a constant value indicating the buttons and icons to display on the message box. If you do not specify a button constant, the MsgBox function uses the default vbOKOnly that displays only the OK button.

The optional Title argument contains the text that displays on the title bar of the message box. If you omit this argument, Excel displays a default value of Microsoft

Excel.

The final two optional arguments are available for adding help to the message box. The Helpfile argument specifies the name of the help file and the Context argument specifies the context ID of the help topic to display.

USING THE MSGBOX FUNCTION

USING THE MSGBOX FUNCTION

-D Create a new subroutine.

B Type Dim MsgVar As Integer, replacing MsgVar with the variable to receive the MsgBox return value.

Declare other variables needed for the subroutine.

Type MsgVar = MsgBox("Text Prompt", buttons, "Text Title"), replacing "Text Prompt" with the prompt for the message box, buttons with the button constant, and "Text Title" with the title for the message box.

■ You can type + to separate multiple button constant values.

MSGBOX RETURN VALUE

CONSTANT

DESCRIPTION

1

vbOK

OK button pressed

2

vbCancel

Cancel button pressed

3

vbAbort

Abort button pressed

4

vbRetry

Retry button pressed

5

vbIgnore

Ignore button pressed

6

vbYes

Yes button pressed

7

vbNo

No button pressed

□ Switch to Excel and run the macro.

■ The message box displays and the macro processes the value of the button pressed.

0 Type a conditional statement to test the value returned from the MsgBox function.

■ In this example, the If Then statement determines if a value of 6 or 7 returns.

0 Type additional code for subroutine.

■ You can type & to join the text string with a variable value.

□ Switch to Excel and run the macro.

■ The message box displays and the macro processes the value of the button pressed.

0 0

Post a comment