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

• 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)

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"

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)

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

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

REFERENCES

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

    built-in dialog boxes