HERBERS Excel-Forum - die Dialoge

Thema: Excel-Dialoge



Aus VBA heraus kann der größte Teil der Excel-Dialoge aufgerufen werden. Nicht alle Dialoge sind funktionsfähig. Zum Teil benötigen sie eine bestimmte Umgebung, um erwartete Aktionen ausführen zu können - beispielsweise können Dialoge zu Diagrammen nur dann wirksam werden, wenn ein Diagramm vorhanden ist -, zum Teil werden - wie z.B. bei einer Farbauswahl - nicht die erwarteten Werte zurückgeliefert, oder die Dialoge sind wie z.B. der xlDialogOpenText einfach nur nicht aufrufbar. Zurückgeliefert wird immer nur True für das Klicken der OK- und False für das Klicken der Abbrechen-Schaltfläche.

In der zugehörigen Beispielarbeitsmappe können Sie durch einfaches Anklicken der jeweiligen Dialoge den Dialogaufruf testen.

Die Excel-Dialoge können als Element der Klasse XlBuiltInDialog sowohl mit ihrem Namen - z.B. xlDialogActivate - wie auch mit ihrem Wert - in diesem Fall 103 - aufgerufen werden. Dem Aufruf können Argumente mitgegeben werden.

Eine Auflistung aller Excel-Dialoge mit ihren Indizes und ihren Argumenten erhalten Sie hier:
Excel-Dialoge

Die eingebauten Excel-Dialoge sind nicht deckungsgleich mit den im manuellen Arbeiten in Excel bekannten Dialogen. Wird beispielsweise der Optionen-Dialog über das Extras-Menü aufgerufen, besteht er aus verschiedenen Registern, aus denen ausgewählt werden kann:

Abbildung - Optionen-Dialog mit allen Registern
Optionen-Dialog

Die Register des Optionen-Dialogs können über VBA einzeln aufgerufen werden, was zur benutzerfreundlichkeit beitragen kann.


Der Aufruf des Berechnung-Registers aus dem Optionen-Dialog:
Sub SHOWxlDialogOptionsCalculation()
   Application.Dialogs(xlDialogOptionsCalculation).Show
End Sub

Register Berechnung

Dem Aufruf können Parameter mitgegeben werden, um damit die Aufrufeinstellungen zu beeinflussen. Für den xlDialogOptionsCalculation stehen zur Verfügung:
ParameterBezieht sich aufVariante AVariante BVariante C
type_numBerechnung1 - Automatisch2 - Automatisch außer bei MehrfachoperationenManuell
iterIteration0 - deaktiviert1 - aktiviert 
max_numMaximale Iterationszahlvariabel  
max_changeMaximale Änderungvariabel  
updateRemotebezüge aktualisieren0 - deaktiviert1 - aktiviert 
precisionGenauigkeit wie angezeigt0 - deaktiviert1 - aktiviert 
date_19041904-Datumswerte0 - deaktiviert1 - aktiviert 
calc_saveExterne Verknüpfungswerte speichern0 - deaktiviert1 - aktiviert 
save_valuesBeschriftungen in Formeln zulassen0 - deaktiviert1 - aktiviert 



Der Code mit dem Dialogindex und Parameterübergabe:

Sub SHOWxlDialogOptionsCalculationB()
   Application.Dialogs(318).Show 1, 1, 10, 0.002, 1, 1, 1, 1, 1, 1
End Sub

Dialogaufruf mit Parameterübergabe

Die Excel-Dialoge

DialogIndexArgumente
xlDialogActivate103window_text, pane_num
xlDialogActiveCellFont476font, font_style, size, strikethrough, superscript, subscript, outline, shadow, underline, color, normal, background, start_char, char_count
xlDialogAddChartAutoformat390name_text, desc_text
xlDialogAddinManager321operation_num, addinname_text, copy_logical
xlDialogAlignment43horiz_align, wrap, vert_align, orientation, add_indent
xlDialogApplyNames133name_array, ignore, use_rowcol, omit_col, omit_row, order_num, append_last
xlDialogApplyStyle212style_text
xlDialogAppMove170x_num, y_num
xlDialogAppSize171x_num, y_num
xlDialogArrangeAll12arrange_num, active_doc, sync_horiz, sync_vert
xlDialogAssignToObject213macro_ref
xlDialogAssignToTool293bar_id, position, macro_ref
xlDialogAttachText80attach_to_num, series_num, point_num
xlDialogAttachToolbars323 
xlDialogAutoCorrect485correct_initial_caps, capitalize_days
xlDialogAxes78x_primary, y_primary, z_primary
xlDialogBorder45outline, left, right, top, bottom, shade, outline_color, left_color, right_color, top_color, bottom_color
xlDialogCalculation32type_num, iter, max_num, max_change, update, precision, date_1904, calc_save, save_values, alt_exp, alt_form
xlDialogCellProtection46locked, hidden
xlDialogChangeLink166old_text, new_text, type_of_link
xlDialogChartAddData392ref, rowcol, titles, categories, replace, series
xlDialogChartTrend350type, ord_per, forecast, backcast, intercept, equation, r_squared, name
xlDialogChartWizard288long, ref, gallery_num, type_num, plot_by, categories, ser_titles, legend, title, x_title, y_title, z_title, number_cats, number_titles
xlDialogCheckboxProperties435value, link, accel_text, accel2_text, 3d_shading
xlDialogClear52type_num
xlDialogColorPalette161file_text
xlDialogColumnWidth47width_num, reference, standard, type_num, standard_num
xlDialogCombination73type_num
xlDialogConsolidate191source_refs, function_num, top_row, left_col, create_links
xlDialogCopyChart147size_num
xlDialogCopyPicture108appearance_num, size_num, type_num
xlDialogCreateNames62top, left, bottom, right
xlDialogCreatePublisher217file_text, appearance, size, formats
xlDialogCustomizeToolbar276category
xlDialogDataDelete36 
xlDialogDataLabel379show_option, auto_text, show_key
xlDialogDataSeries40rowcol, type_num, date_num, step_value, stop_value, trend
xlDialogDefineName61name_text, refers_to, macro_type, shortcut_text, hidden, category, local
xlDialogDefineStyle229style_text, attribute_num, additional_def_args, ...
xlDialogDeleteFormat111format_text
xlDialogDeleteName110name_text
xlDialogDemote203row_col
xlDialogDisplay27cell, formula, value, format, protection, names, precedents, dependents, note
xlDialogEditboxProperties438validation_num, multiline_logical, vscroll_logical, password_logical
xlDialogEditColor223color_num, red_value, green_value, blue_value
xlDialogEditDelete54shift_num
xlDialogEditionOptions251edition_type, edition_name, reference, option, appearance, size, formats
xlDialogEditSeries228series_num, name_ref, x_ref, y_ref, z_ref, plot_order
xlDialogErrorbarX463include, type, amount, minus
xlDialogErrorbarY464include, type, amount, minus
xlDialogExtract35unique
xlDialogFileDelete6file_text
xlDialogFillGroup200type_num
xlDialogFillWorkgroup301type_num
xlDialogFilterAdvanced370operation, list_ref, criteria_ref, copy_ref, unique
xlDialogFindFile475 
xlDialogFont26name_text, size_num
xlDialogFontProperties381font, font_style, size, strikethrough, superscript, subscript, outline, shadow, underline, color, normal, background, start_char, char_count
xlDialogFormatAuto269format_num, number, font, alignment, border, pattern, width
xlDialogFormatChart465layer_num, view, overlap, angle, gap_width, gap_depth, chart_depth, doughnut_size, axis_num, drop, hilo, up_down, series_line, labels, vary
xlDialogFormatCharttype423apply_to, group_num, dimension, type_num
xlDialogFormatFont150name_text, size_num, bold, italic, underline, strike, color, outline, shadow, object_id_text, start_num, char_num
xlDialogFormatLegend88position_num
xlDialogFormatMain225type_num, view, overlap, gap_width, vary, drop, hilo, angle, gap_depth, chart_depth, up_down, series_line, labels, doughnut_size
xlDialogFormatMove128explosion_num
xlDialogFormatNumber42format_text
xlDialogFormatOverlay226type_num, view, overlap, gap_width, vary, drop, hilo, angle, series_dist, series_num, up_down, series_line, labels, doughnut_size
xlDialogFormatSize129x_off, y_off, reference
xlDialogFormatText89x_align, y_align, orient_num, auto_text, auto_size, show_key, show_value, add_indent
xlDialogFormulaFind64text, in_num, at_num, by_num, dir_num, match_case, match_byte
xlDialogFormulaGoto63reference, corner
xlDialogFormulaReplace130find_text, replace_text, look_at, look_by, active_cell, match_case, match_byte
xlDialogFunctionWizard450 
xlDialogGallery3dArea193type_num
xlDialogGallery3dBar272type_num
xlDialogGallery3dColumn194type_num
xlDialogGallery3dLine195type_num
xlDialogGallery3dPie196type_num
xlDialogGallery3dSurface273type_num
xlDialogGalleryArea67type_num, delete_overlay
xlDialogGalleryBar68type_num, delete_overlay
xlDialogGalleryColumn69type_num, delete_overlay
xlDialogGalleryCustom388name_text
xlDialogGalleryDoughnut344type_num, delete_overlay
xlDialogGalleryLine70type_num, delete_overlay
xlDialogGalleryPie71type_num, delete_overlay
xlDialogGalleryRadar249type_num, delete_overlay
xlDialogGalleryScatter72type_num, delete_overlay
xlDialogGoalSeek198target_cell, target_value, variable_cell
xlDialogGridlines76x_major, x_minor, y_major, y_minor, z_major, z_minor, 2D_effect
xlDialogInsert55shift_num
xlDialogInsertObject259object_class, file_name, link_logical, display_icon_logical, icon_file, icon_number, icon_label
xlDialogInsertPicture342file_name, filter_number
xlDialogInsertTitle380chart, y_primary, x_primary, y_secondary, x_secondary
xlDialogLabelProperties436accel_text, accel2_text, 3d_shading
xlDialogListboxProperties437range, link, drop_size, multi_select, 3d_shading
xlDialogMacroOptions382macro_name, description, menu_on, menu_text, shortcut_on, shortcut_key, function_category, status_bar_text, help_id, help_file
xlDialogMailEditMailer470to_recipients, cc_recipients, bcc_recipients, subject, enclosures, which_address
xlDialogMailLogon339name_text, password_text, download_logical
xlDialogMailNextLetter378 
xlDialogMainChart85type_num, stack, 100, vary, overlap, drop, hilo, overlap%, cluster, angle
xlDialogMainChartType185type_num
xlDialogMenuEditor322 
xlDialogMove262x_pos, y_pos, window_text
xlDialogNew119type_num, xy_series, add_logical
xlDialogNote154add_text, cell_ref, start_char, num_chars
xlDialogObjectProperties207placement_type, print_object
xlDialogObjectProtection214locked, lock_text
xlDialogOpen1file_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
xlDialogOpenLinks2document_text1, document_text2, ..., read_only, type_of_link
xlDialogOpenMail188subject, comments
xlDialogOpenText441file_name, file_origin, start_row, file_type, text_qualifier, consecutive_delim, tab, semicolon, comma, space, other, other_char, field_info
xlDialogOptionsCalculation318type_num, iter, max_num, max_change, update, precision, date_1904, calc_save, save_values
xlDialogOptionsChart325display_blanks, plot_visible, size_with_window
xlDialogOptionsEdit319incell_edit, drag_drop, alert, entermove, fixed, decimals, copy_objects, update_links, move_direction, autocomplete, animations
xlDialogOptionsGeneral356R1C1_mode, dde_on, sum_info, tips, recent_files, old_menus, user_info, font_name, font_size, default_location, alternate_location, sheet_num, enable_under
xlDialogOptionsListsAdd458import_ref, by_row
xlDialogOptionsTransition355menu_key, menu_key_action, nav_keys, trans_eval, trans_entry
xlDialogOptionsView320formula, status, notes, show_info, object_num, page_breaks, formulas, gridlines, color_num, headers, outline, zeros, hor_scroll, vert_scroll, sheet_tabs
xlDialogOutline142auto_styles, row_dir, col_dir, create_apply
xlDialogOverlay86type_num, stack, 100, vary, overlap, drop, hilo, overlap%, cluster, angle, series_num, auto
xlDialogOverlayChartType186type_num
xlDialogPageSetup7head, foot, left, right, top, bot, orient, paper_size, scale, quality, head_margin, foot_margin, pg_num
xlDialogParse91parse_text, destination_ref
xlDialogPasteSpecial53format_text, pastelink_logical, display_icon_logical, icon_file, icon_number, icon_label
xlDialogPatterns84type, picture_units, apply
xlDialogPivotFieldGroup433start, end, by, periods
xlDialogPivotFieldProperties313name, pivot_field_name, new_name, orientation, function, formats
xlDialogPivotFieldUngroup434 
xlDialogPivotShowPages421name, page_field
xlDialogPivotTableWizard312type, source, destination, name, row_grand, col_grand, save_data, apply_auto_format, auto_page, reserved
xlDialogPlacement300placement_type
xlDialogPrint8range_num, from, to, copies, draft, preview, print_what, color, feed, quality, y_resolution, selection, printer_text, print_to_file, collate
xlDialogPrinterSetup9printer_text
xlDialogPrintPreview222 
xlDialogPromote202rowcol
xlDialogProperties474title, subject, author, keywords, comments
xlDialogProtectDocument28contents, windows, password, objects, scenarios
xlDialogPushbuttonProperties445default_logical, cancel_logical, dismiss_logical, help_logical, accel_text, accel_text2
xlDialogReplaceFont134font_num, name_text, size_num, bold, italic, underline, strike, color, outline, shadow
xlDialogRoutingSlip336recipients, subject, message, route_num, return_logical, status_logical
xlDialogRowHeight127height_num, reference, standard_height, type_num
xlDialogRun17reference, step
xlDialogSaveAs5document_text, type_num, prot_pwd, backup, write_res_pwd, read_only_rec
xlDialogSaveCopyAs456document_text
xlDialogSaveNewObject208 
xlDialogSaveWorkbook145document_text, type_num, prot_pwd, backup, write_res_pwd, read_only_rec
xlDialogSaveWorkspace285name_text
xlDialogScale87min_num, max_num, major, minor, cross, logarithmic, reverse, min
xlDialogScenarioAdd307scen_name, value_array, changing_ref, scen_comment, locked, hidden
xlDialogScenarioCells305changing_ref
xlDialogScenarioEdit308scen_name, new_scenname, value_array, changing_ref, scen_comment, locked, hidden
xlDialogScenarioMerge473source_file
xlDialogScenarioSummary311result_ref, report_type
xlDialogScrollbarProperties420value, min, max, inc, page, link, 3d_shading
xlDialogSelectSpecial132type_num, value_type, levels
xlDialogSendMail189recipients, subject, return_receipt
xlDialogSeriesAxes460axis_num
xlDialogSeriesOrder466chart_num, old_series_num, new_series_num
xlDialogSeriesX461x_ref
xlDialogSeriesY462name_ref, y_ref
xlDialogSetPrintTitles23titles_for_cols_ref, titles_for_rows_ref
xlDialogSetUpdateStatus159link_text, status, type_of_link
xlDialogShowDetail204rowcol, rowcol_num, expand, show_field
xlDialogShowToolbar220bar_id, visible, dock, x_pos, y_pos, width, protect, tool_tips, large_buttons, color_buttons
xlDialogSize261width, height, window_text
xlDialogSort39orientation, key1, order1, type, custom
xlDialogSortSpecial192sort_by, method, key1, order1, key2, order2, key3, order3, header, order, case
xlDialogSplit137col_split, row_split
xlDialogStandardFont190name_text, size_num, bold, italic, underline, strike, color, outline, shadow
xlDialogStandardWidth472standard_num
xlDialogStyle44bold, italic
xlDialogSubscribeTo218file_text, format_num
xlDialogSubtotalCreate398at_change_in, function_num, total, replace, pagebreaks, summary_below
xlDialogSummaryInfo474title, subject, author, keywords, comments
xlDialogTable41row_ref, column_ref
xlDialogTabOrder394 
xlDialogTextToColumns422destination_ref, data_type, text_delim, consecutive_delim, tab, semicolon, comma, space, other, other_char, field_info
xlDialogUnhide94window_text
xlDialogUpdateLink201link_text, type_of_link
xlDialogVbaInsertFile328filename_text
xlDialogVbaMakeAddin478 
xlDialogVbaProcedureDefinition330 
xlDialogView3d197elevation, perspective, rotation, axes, height%, autoscale
xlDialogWindowMove14x_pos, y_pos, window_text
xlDialogWindowSize13width, height, window_text
xlDialogWorkbookAdd281name_array, dest_book, position_num
xlDialogWorkbookCopy283name_array, dest_book, position_num
xlDialogWorkbookInsert354type_num
xlDialogWorkbookMove282name_array, dest_book, position_num
xlDialogWorkbookName386oldname_text, newname_text
xlDialogWorkbookNew302 
xlDialogWorkbookOptions284sheet_name, bound_logical, new_name
xlDialogWorkbookProtect417structure, windows, password
xlDialogWorkbookTabSplit415ratio_num
xlDialogWorkbookUnhide384sheet_text
xlDialogWorkgroup199name_array
xlDialogWorkspace95fixed, decimals, r1c1, scroll, status, formula, menu_key, remote, entermove, underlines, tools, notes, nav_keys, menu_key_action, drag_drop, show_info
xlDialogZoom256magnification