Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
|
| < 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, TrueEnd Sub
| Note | 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"), TrueEnd 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.
-
Open a VBA module.
-
Press F2 to open the Object Browser.
-
Type xlDialog into the search text box.
-
Click the find button to execute the search.
Figure 18-5 displays the result after the search.
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.
| Tip | 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.
|
Table 18-1 lists some of the common dialog boxes used to format cells in the workbook.
| xlDialog Box | Description |
|---|---|
| xlDialogActiveCellFont | font, font_style, size, strikethrough, superscript, subscript, outline, shadow, underline, color, normal, background, start_char, char_count |
| xlDialogAlignment | horiz_align, wrap, vert_align, orientation, add_indent |
| xlDialogBorder | outline, left, right, top, bottom, shade, outline_color, left_color, right_color, top_color, bottom_color |
| xlDialogCellProtection | locked, hidden |
| xlDialogFont | name_text, size_num |
| xlDialogFontProperties | font, font_style, size, strikethrough, superscript, subscript, outline, shadow, underline, color, normal, background, start_char, char_count |
| xlDialogFormatFont | name_text, size_num, bold, italic, underline, strike, color, outline, shadow |
| xlDialogPatterns | apattern, afore, aback, newui |
Table 18-2 lists some of the common dialog boxes used to insert or modify charts in a workbook.
| xlDialog Box | Description |
|---|---|
| xlDialogAddChartAutoformat | name_text, desc_text |
| xlDialogAxes | x_primary, y_primary, x_secondary, y_secondary |
| xlDialogChartAddData | ref, rowcol, titles, categories, replace, series |
| xlDialogChartWizard | long, ref, gallery_num, type_num, plot_by, categories, ser_titles, legend, title, x_title, y_title, z_title, number_cats, number_titles |
| xlDialogDataLabel | show_option, auto_text, show_key |
| xlDialogDataSeries | rowcol, type_num, date_num, step_value, stop_value, trend |
| xlDialogEditSeries | series_num, name_ref, x_ref, y_ref, z_ref, plot_order |
| xlDialogFormatChart | layer_num, view, overlap, angle, gap_width, gap_depth, chart_depth, doughnut_size, axis_num, drop, hilo, up_down, series_line, labels, vary |
| xlDialogFormatCharttype | apply_to, group_num, dimension, type_num |
| xlDialogFormatLegend | position_num |
| xlDialogGallery3dBar | type_num |
| xlDialogGallery3dColumn | type_num |
| xlDialogGallery3dLine | type_num |
| xlDialogGallery3dPie | type_num |
| xlDialogGalleryDoughnut | type_num, delete_overlay |
| xlDialogGalleryLine | type_num, delete_overlay |
| xlDialogGalleryPie | type_num, delete_overlay |
| xlDialogMainChartType | type_num |
Table 18-3 lists some of the common dialog boxes used to access a variety of options available using Excel's menu structure.
| xlDialog Box | Description |
|---|---|
| xlDialogApplyNames | name_array, ignore, use_rowcol, omit_col, omit_row, order_num, append_last |
| xlDialogAutoCorrect | correct_initial_caps, capitalize_days |
| xlDialogColorPalette | file_text |
| xlDialogColumnWidth | width_num, reference, standard, type_num, standard_num |
| xlDialogCreateNames | top, left, bottom, right |
| xlDialogDefineName | name_text, refers_to, macro_type, shortcut_text, hidden, category, local |
| xlDialogDefineStyle | style_text, number, font, alignment, border, pattern, protection |
| xlDialogFilterAdvanced | operation, list_ref, criteria_ref, copy_ref, unique |
| xlDialogGoalSeek | target_cell, target_value, variable_cell |
| xlDialogInsertObject | object_class, file_name, link_logical, display_icon_logical, icon_file, icon_number, icon_label |
| xlDialogOpen | 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 |
| 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 limited 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 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, 2End Sub
Figure 18-6 displays the Alignment dialog box with the arguments set as indicated by the preceding procedure.
|
| < Day Day Up > |
|