Page 412 - Microsoft Office Excel 2003 Programming Inside Out

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

It’s important to understand that built-in dialog boxes are not documented very well. The
online help is very sketchy, and the files do not mention the fact that displaying one of Excel’s
dialog boxes using VBA code might not always work exactly the same as using a menu com­
mand to display the dialog box. Consequently, you might have to do some experimentation
to make sure your code performs as it should.
In the case of the Go To dialog box, you will notice that the Special button is grayed out when
the dialog is shown using a VBA statement. This button normally displays the Go To Special
dialog box. To display the Go To Special dialog box using the VBA code, you need to use the
intrinsic constant for that dialog box, xlDialogSelectSpecial. The results are shown in Figure
18-2.

Sub ShowGotoSpecial()
Application.Dialogs(xlDialogSelectSpecial).Show
End Sub

Figure 18-2. Displaying the Go To Special dialog box requires you to use the intrinsic
constant for that dialog box.
Another potential problem you might encounter when you work with the built-in dialog
boxes is that you are not able to display some tabbed dialog boxes as they appear when you
display them using the menu system. For example, there’s no way to show the Format Cells
dialog box with the tabs. Rather, you can only show one tab at a time. The following state­
ment displays the Alignment tab of the Format Cells dialog box (with the results shown in
Figure 18-3).

Sub ShowAlignmentTab()
Application.Dialogs(xlDialogAlignment).Show
End Sub

To show other tabs in the Format Cells dialog box, use any of these constants: xlDialogFormat-
Number, xlDialogBorder, xlDialogCellProtection, xlDialogPatterns, or xlDialogFontProperties.
Notice that there is no consistency in the naming of these constants.
Chapter 18

386