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.

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"), 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.

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.

Note 

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

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.

Table 18-2: xlDialog Boxes Used to Modify Charts

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.

Table 18-3: xlDialog Boxes Found in 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, 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

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net