Modifying Existing Dialog Boxes

 < Day Day Up > 

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 directory 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

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


The arguments for each of the built-in dialog boxes are listed in online help. To locate the help topic, search for Built-In Dialog Box Argument Lists. Unfortunately, the help topic provides no explanation of what the arguments are used for.

According to the help file, the Go To dialog box executed by the xlDialogFormulaGoto constant uses two arguments: Reference and Corner. The Reference argument provides a default range that appears in the Reference box. The Corner argument is a value you set to either True or False to specify whether to display the target cell so that it appears in the upper-left corner of the window. The following example uses both of these arguments:

Sub GotoArguments()
Application.Dialogs(xlDialogFormulaGoto).Show Range("Z100"), True
End Sub

As you work through the dialog boxes, you will find that some trial and error is required to successfully use the Dialogs collection.

Exploring the Dialogs Collection

The Dialogs collection of the Application object consists of more than 250 members that represent most of Excel's built-in dialog boxes. Each Dialog object has a predefined constant to make it easy to specify the dialog box that you need.

To get a complete list of the dialog box constants available, use the Object Browser. Follow these steps to display the members of the Dialogs collection in the Object Browser.

  1. Open a VBA module.

  2. Press F2 to open the Object Browser.

  3. Type xlDialog into the search text box.

  4. Click the find button to execute the search.

Figure 18-5 displays the result after the search.

click to expand
Figure 18-5: The Object Browser displays the search results for the built-in dialog boxes available in Excel.

There are more than 250 dialog boxes you can call using the xlDialog intrinsic constants, but some of them are more useful than others. The following three tables list dialog boxes used to format cells, modify charts, and perform other miscellaneous but useful tasks.


Check Your Work Against the interface
The arguments for each dialog box aren't spelled out well in the online help files or in any other available literature, but in many cases an argument will correspond to a check box, an option button, or another control that appears somewhere in the dialog box. For example, the xlDialogFont constant calls the Fonts dialog box. You select the Tahoma font and a size of 12 by default using the code.


Application.Dialogs(xlDialogFont).Show "Tahoma", 12.
To display the default font, you would leave the first argument blank, as in the statement Application.Dialogs(xlDialogFont).Show, 12.

Table 18-1 lists some of the common dialog boxes used to format cells in the workbook.

Table 18-1: xlDialog Boxes Used to Format Cells

xlDialog Box



font, font_style, size, strikethrough, superscript, subscript, outline, shadow, underline, color, normal, background, start_char, char_count


horiz_align, wrap, vert_align, orientation, add_indent


outline, left, right, top, bottom, shade, outline_color, left_color, right_color, top_color, bottom_color


locked, hidden


name_text, size_num


font, font_style, size, strikethrough, superscript, subscript, outline, shadow, underline, color, normal, background, start_char, char_count


name_text, size_num, bold, italic, underline, strike, color, outline, shadow


apattern, afore, aback, newui

Table 18-2 lists some of the common dialog boxes used to insert or modify charts in a workbook.

Table 18-2: xlDialog Boxes Used to Modify Charts

xlDialog Box



name_text, desc_text


x_primary, y_primary, x_secondary, y_secondary


ref, rowcol, titles, categories, replace, series


long, ref, gallery_num, type_num, plot_by, categories, ser_titles, legend, title, x_title, y_title, z_title, number_cats, number_titles


show_option, auto_text, show_key


rowcol, type_num, date_num, step_value, stop_value, trend


series_num, name_ref, x_ref, y_ref, z_ref, plot_order


layer_num, view, overlap, angle, gap_width, gap_depth, chart_depth, doughnut_size, axis_num, drop, hilo, up_down, series_line, labels, vary


apply_to, group_num, dimension, type_num












type_num, delete_overlay


type_num, delete_overlay


type_num, delete_overlay



Table 18-3 lists some of the common dialog boxes used to access a variety of options available using Excel's menu structure.

Table 18-3: xlDialog Boxes Found in Excel's Menu Structure

xlDialog Box



name_array, ignore, use_rowcol, omit_col, omit_row, order_num, append_last


correct_initial_caps, capitalize_days




width_num, reference, standard, type_num, standard_num


top, left, bottom, right


name_text, refers_to, macro_type, shortcut_text, hidden, category, local


style_text, number, font, alignment, border, pattern, protection


operation, list_ref, criteria_ref, copy_ref, unique


target_cell, target_value, variable_cell


object_class, file_name, link_logical, display_icon_logical, icon_file, icon_number, icon_label


file_text, update_links, read_only, format, prot_pwd, write_res_pwd, ignore_rorec, file_origin, custom_delimit, add_logical, editable, file_access, notify_logical, converter


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


document_text, type_num, prot_pwd, backup, write_res_pwd, read_only_rec


recipients, subject, return_receipt


bar_id, visible, dock, x_pos, y_pos, width, protect, tool_tips, large_buttons, color_buttons



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 limited to how the dialog box displays by default. You are able to modify the default settings by passing arguments to the dialog box.


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 identify the individual dialog box, which is done using the Dialogs property with an xlDialog constant. 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

Figure 18-6 displays the Alignment dialog box with the arguments set as indicated by the preceding procedure.

click to expand
Figure 18-6: The Alignment dialog box with the Horizontal and Vertical alignment set to centered, as well as Wrap Text set to true.

 < Day Day Up > 

Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: