Page 410 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Microsoft Office Excel 2003 Programming Inside Out

Displaying Existing Dialog Boxes

The procedures that you write in VBA can execute Excel’s menu commands, and if those
commands lead to a dialog box, your code can apply settings to the dialog box. You can also
access Excel’s built-in dialog boxes using the Dialog object. An interesting and very useful fact
about working with dialog boxes is that you can execute your dialog box routines completely
behind the scenes; the dialog box doesn’t need to be in view to apply the settings.
The Dialogs collection represents the list of dialog boxes that are built into Excel. The xlBuilt­
inDialog constant is used to access an individual Dialog object in the Dialogs collection. Use
the syntax Dialogs(xlDialogObjectName), where xlDialogObjectName is the built-in constant
identifying the dialog box you want to open. The following example displays the built-in File
Open dialog box:

Sub ShowOpen()
Result = Application.Dialogs(xlDialogOpen).Show
End Sub

The Result variable lets you determine if the user clicked the OK button or if the action was
cancelled by pressing the Esc key or by the user clicking the Cancel button. You can then use
the value assigned to the variable to specify the next action that should occur based on the
user’s input.
The Excel Object Library includes intrinsic constants for many of the built-in dialog boxes.
Each constant is formed from the prefix xlDialog followed by the name of the dialog box. For
example, the Data Validation dialog box constant is xlDialogDataValidation, and the Define
Name dialog box constant is xlDialogDefineName. These constants are examples of the type
of members found in the xlBuiltinDialog property.

For more information about, and a complete list of, the available xlDialog constants, type built-in
dialog boxes in the Visual Basic Editor Ask a Question box and click the Built-in Dialog Box Argument
Lists help topic.

A Dialog object represents a single built-in Excel dialog box. Each Dialog object will have
additional custom properties depending on what type of Dialog object it is. Besides the typi­
cal collection attributes, the Dialogs collection also has a Count property that returns the
number of Dialog objects in the collection.
For example, the following VBA statement is equivalent to clicking Edit, Go To, and specify­
ing the range A1:C3 and clicking OK. However, when you use the VBA code, the Go To dialog
box does not need to appear, so the action is seamless to the user.

Sub GotoRange()
Application.Goto Reference:=Range("A1:C3")
End Sub

Chapter 18

384