Page 415 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Customizing Dialog Boxes

Note The arguments for each of the built-in dialog boxes are listed in online help. To
locate the help topic, search for Built-In Dialog Box Argument Lists. Unfortunately, the help
topic provides no explanation of what the arguments are used for.

According to the help file, the Go To dialog box executed by the xlDialogFormulaGoto con­
stant uses two arguments: Reference and Corner. The Reference argument provides a default
range that appears in the Reference box. The Corner argument is a value you set to either True
or False to specify whether to display the target cell so that it appears in the upper-left corner
of the window. The following example uses both of these arguments:

Sub GotoArguments()
Application.Dialogs(xlDialogFormulaGoto).Show Range("Z100"), True
End Sub

As you work through the dialog boxes, you will find that some trial and error is required to
successfully use the Dialogs collection.

Exploring the Dialogs Collection

The Dialogs collection of the Application object consists of more than 250 members that rep­
resent most of Excel’s built-in dialog boxes. Each Dialog object has a predefined constant to
make it easy to specify the dialog box that you need.
To get a complete list of the dialog box constants available, use the Object Browser. Follow
these steps to display the members of the Dialogs collection in the Object Browser.
1 Open a VBA module.
2 Press F2 to open the Object Browser.
3 Type xlDialog into the search text box.
4 Click the find button to execute the search.

Chapter 18

389