Page 419 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Customizing Dialog Boxes

Table 18-3. xlDialog Boxes Found in Excel’s Menu Structure
xlDialog Box Description
xlDialogPageSetup head, foot, left, right, top, bot, hdng, grid, h_cntr, v_cntr,
orient, paper_size, scale, pg_num, pg_order, bw_cells,
quality, head_margin, foot_margin, notes, draft
xlDialogSaveAs document_text, type_num, prot_pwd, backup,
write_res_pwd, read_only_rec
xlDialogSendMail recipients, subject, return_receipt
xlDialogShowToolbar bar_id, visible, dock, x_pos, y_pos, width, protect, tool_tips,
large_buttons, color_buttons
xlDialogZoom magnification

There are many other built-in dialog boxes available. To locate all the arguments available to
the built-in dialog boxes, search the MSDN Web site and online help.

Passing Arguments to Existing Dialog Boxes

At times, a dialog box is your solution; the dialog box will allow the user to interact with a
familiar dialog box when the Show property is set to True. Keep in mind that you are not lim­
ited to how the dialog box displays by default. You are able to modify the default settings by
passing arguments to the dialog box.

Note It’s not very efficient to use a Dialog object to return or change a value for a dialog
box when you can return or change it using a property or method. Keep in mind that when
VBA code is used in place of accessing the Dialog object, the code is simpler and shorter.

Prior to returning or changing a dialog box setting using the Dialog object, you need to iden­
tify the individual dialog box, which is done using the Dialogs property with an xlDialog con­
stant. After you have initiated a Dialog object, you can return or set options in the dialog box.
For example, if you want the user to be able to verify the settings that will be applied to a
range of cells but you also want to minimize the user’s interaction, you can pass the settings
to the dialog box so that they are automatically selected. To display the Alignment dialog box
such that it is ready to format the selected text centered top and bottom with word wrap
turned on, you can use the following code:

Sub VerifyAlignment()
Application.Dialogs(xlDialogAlignment).Show 3, 1, 2
End Sub
Chapter 18

393