Page 414 - Microsoft Office Excel 2003 Programming Inside Out

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

You can also save the workbook with the new file name using the following line of code:
Workbooks.SaveAs FullFileName

When the users have confirmed the FileOpen dialog or the FileSaveAs dialog, they might
have changed the active or current folder. Using the FileOpen and FileSaveAs dialog boxes
lets the users choose the current folder and navigate the drives of their computers. This is
the easiest method to let users choose the destination for their files.

Modifying Existing Dialog Boxes

There will be times when you want to use an existing dialog box; however, you might want to
set the default choices within the dialog box. For example, if you want to set a default direc­
tory where a workbook will be saved, you can use a dialog box’s arguments to modify the
default selections, but you’ll be heading into uncharted territory. The following section
explains how to access the Dialogs collection and pass arguments to the dialog box.
Most of the built-in dialog boxes also accept arguments, which typically correspond to the
controls on the dialog box. For example, the Cell Protection dialog box, which is executed by
the xlDialogCellProtection constant, has two arguments associated with it: locked and hidden.
If you want to display that dialog box with both of these options checked, you would use the
following statement to set both of the arguments to True. The results are shown in Figure 18-4.

Sub ProtectionArgs()
Application.Dialogs(xlDialogCellProtection).Show True, True
End Sub

Figure 18-4. Both check boxes in the Cell Protection dialog box can be selected by default
Chapter 18
using the proper VBA code.

388