Microsoft Office Tutorials and References
In Depth Information
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
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.
Figure 18-1. The Go To dialog box appears when called using the xlDialogFormulaGoto
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.
Result = Application.Dialogs(xlDialogFormulaGoto).Show
Contrary to what you might expect, the Result variable does not hold the range that was spec
ified in the Go To dialog box. Instead, as stated, the variable holds a Boolean value that
reflects how the dialog box was dismissed.