About the Dialogs collection

The Dialogs collection of the Application object consists of more than 200 members that represent most of Excel's built-in dialog boxes. Each has a predefined constant to make it easy to specify the dialog box that you need. For example, Excel's Go To dialog box is represented by the constant xlDialogFormulaGoto.

Use the Show method to actually display the dialog box. Here's an example that displays the Go To dialog box (see Figure 12-11):

Go To

?

Q

Go to:

Inference;

Special,,.

OK

Cancel

Figure 12-11: This dialog box was displayed with a VBA statement.

Figure 12-11: This dialog box was displayed with a VBA statement.

Application.Dialogs(xlDialogFormulaGoto).Show

When the Go To dialog box is shown, the user can specify a named range or enter a cell address to go to. This dialog box is the one that appears when you choose Home Editing Find & Select Go To (or press F5).

You can also write code to determine how the user dismissed the dialog box. Do this by using a variable. In the following statement, the Result variable will be True if the user clicks OK and False if the user clicks Cancel or presses Esc.

Result = Application.Dialogs(xlDialogFormulaGoto).Show

Note Contrary to what you might expect, the Result variable does not hold the range that was specified in the Go To dialog box.

The statement below displays the Open dialog box (equivalent to selecting Office

Application.Dialogs(xlDialogOpen).Show

Unfortunately, the Dialogs collection is poorly documented, and the newer dialog boxes aren't even available. The next section describes a better way to display built-in dialog boxes - for Excel 2007 only.

0 0

Post a comment