XL: Setting Initial Values When Running Built-In Dialog Box

ID: Q110670

The information in this article applies to:

Microsoft Excel for Windows, version 5.0
Microsoft Excel for Macintosh, version 5.0
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel, when you use a Visual Basic for Applications procedure to run a built-in dialog box, you must know the correct order of the arguments for the corresponding macro function if you want to set initial values in the dialog box. For example, to set initial values in the Save As dialog box, you must know the order of the arguments for the SAVE.AS macro function.

In addition, when you set initial values in the dialog box, you must either use arguments in their correct order, using commas as placeholders for omitted arguments, or use the Show method named arguments arg1, arg2, through arg30, to specify the order of the arguments.

The named arguments arg1, arg2, through arg30, are for built-in dialog boxes only; they provide the initial values for the dialog box.

MORE INFORMATION

In Visual Basic, arguments names are defined for properties and methods. Because the arguments are named, you can list them in any order or omit arguments without leaving commas as a placeholders.

Using Named Arguments

For example, the following is the syntax for the SaveAs method

    object.SaveAs(filename, fileFormat, password, writeResPassword,
                   readOnlyRecommended, createBackup)

where the SaveAs method arguments are listed in the parentheses.

The following is an instruction in Visual Basic that uses this method with only the filename and createBackup arguments:

   ' Using Named arguments
   ActiveWorkbook.SaveAs createBackup:=True, FileName:="BUDGET.XLS"

Note that you do not have to list the arguments in any particular order, and you do not have to use commas as placeholders for the omitted arguments.

To Display a Built-In Dialog Box with Initial Values

If you use the Show method to display the Save As dialog box, you must know the order of the arguments for the corresponding Save.As macro command to set initial values in the dialog box.

The syntax for the Save.As macro command is the following

   SAVE.AS(document_text, type_num, prot_pwd, backup, write_res_pwd,
           read_only_rec)

where the arguments are included in the parentheses in their correct order. If you want display the Save As dialog box with only the document_text and backup initial values set, you must indicate the missing arguments with commas, or use the named arguments arg1, arg2, through arg30 to specify the correct order.

To display the Save As dialog box with BUDGET.XLS in the File Name box and the Always Create Backup option selected as initial values, use either of the methods below:

Using Commas As Placeholders

' Using arguments in correct order with commas as placeholders

Application.Dialogs(xlDialogSaveAs).Show "BUDGET.XLS", , , True

The extra commas after the filename value are placeholders for the missing arguments.

Note that if you use commas as placeholders for missing arguments, trailing commas at the end of an instruction can be omitted. In the example above, it is not necessary to add additional commas after the final argument used (backup).

-or-

Using Show Method Named Arguments

' Using arg1, arg2, ... arg30 to provide the position of arguments

Application.Dialogs(xlDialogSaveAs).Show arg1:="BUDGET.XLS", arg4:=True

REFERENCES

For more information about the Dialog Object or the Show Method, choose the Search button in Help and type:

    built-in dialog boxes

KBCategory: kbusage KBSubcategory:

Additional reference words: 7.00 5.00 97

Version           : 5.00 5.00c 7.00
Platform          : WINDOWS

Last Reviewed: February 14, 1998