Page 411 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Customizing Dialog Boxes

In some cases, however, you might want to display one of Excel’s built-in dialog boxes so the
user can make the choices. There are two ways to do this:
● Access the Dialogs collection of the Application object.
● Execute a menu item directly.
The Dialogs collection of the Application object represents most of Excel’s built-in dialog
boxes. The Dialogs collection has predefined constants 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 is an example that displays the
Go To dialog box, with the results shown in Figure 18-1.

Sub ShowGoto()
Application.Dialogs(xlDialogFormulaGoto).Show
End Sub

Figure 18-1. The Go To dialog box appears when called using the xlDialogFormulaGoto
intrinsic constant.
When the Go To dialog box is shown, the user can specify a named range or enter a cell
address to go to. The dialog box displayed using the xlDialogFormulaGoto constant is the
same one that appears when you choose the Go To command from the Edit menu.

You can also write code that uses a variable to determine how the user dismissed the dialog
box. In the following statement, the Result variable will be True if the user clicked OK, and
False if the user clicked Cancel or pressed the Esc key.

Sub ShowGoto2()
Result = Application.Dialogs(xlDialogFormulaGoto).Show
End Sub
Contrary to what you might expect, the Result variable does not hold the range that was spec­ Chapter 18
ified in the Go To dialog box. Instead, as stated, the variable holds a Boolean value that
reflects how the dialog box was dismissed.

385