Page 413 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Customizing Dialog Boxes

Figure 18-3. You can display the Format Cells dialog box, albeit one tab page at a time.

Inside Out

Return Variables or Execute?

When a dialog box is opened several options are available. If you execute the dialog box,
the desired actions of the dialog box will occur. If you are only trying to gather information
such as a filename, you should choose another method, such as GetOpenFilename or
GetSaveAsFilename. Both methods display their respective dialogs but don’t actually open
or save the files when the user confirms the dialog box. The methods instead return the
complete filename as a variable for use in later code.
The following example uses the GetOpenFilename and GetSaveAsFilename methods to
return the name of the selected file:

Sub GetFileName()
FullFileName = Application.GetOpenFilename("Excel files (*.xl*), *.xl*", _
1, "Custom Dialog Title", , False)
FullFileName = Application.GetSaveAsFilename("DefaultFilename.xls", _
"Excel files (*.xl*), *.xl*, 1, "Custom Dialog Title")
End Sub
Once the filename has been passed to the variable, it can be used in whichever method
you choose. For example, with the filename it would be simple to open the file using the
Open method.
Chapter 18
Workbooks.Open FullFileName
continued

387