Application.Dialogs

[Rudi]

Hi,

I speak from negligence, but I have always been frustrated by the fact that Excels (Application.Dialogs) option in VBA is so illogical to use. What is the use of been given all the dialogs when one has so much difficulty in storing a value from the dialog into a variable to use.

Take for instance the color palette dialog:

This code does NOT work...

Sub Test()
Dim myCol As Long
Application.Dialogs(xlDialogColorPalette).Show Arg1:=myCol
Range("A1").Interior.Color = myCol
End Sub

Please help with how to show the palette and allow a user to choose a color for use in a cell.

TX

[HansV]

The color palette dialog is not intended to let the user select a color for a cell. It's intended to view and edit the set of colors used in the active workbook. The one you'd probably want is Application.Dialogs(xlDialogPatterns). But it applies to the currently selected cell or cells, you can't use it for a non-selected cell.

[Rudi]

Wow....if I only knew that about two weeks ago. !!!!!!

Is there any way to know which dialogs allow one to store variables (or do they ALL dis-allow this?)

EG:
myChoice = Application.Dialogs(xlDialogWhateverDialog).Show Arg1:=StoreValue

TX

[HansV]

The arguments Arg1, Arg2 etc. are not intended to return a value, they are used to pass a value to the dialog before it is displayed.

Excel dialogs perform their action directly, they don't provide a way to get at the selected options. The only feedback is that the Show method returns True if the user clicked OK, and False if the user clicked Cancel in the dialog.

There are a few dialogs that do provide access to the selected item(s), but they aren't part of the Dialogs collection:

Application.GetOpenFilename displays the Open dialog and returns either the selected filename or False.
Application.GetSaveAsFilename displays the Save As dialog and returns either the specified filename or False.
Application.FileDialog displays an Open, Save As or Select Folder dialog, depending on the properties set by the programmer.

[Rudi]

>>>> they are used to pass a value to the dialog before it is displayed.

Ahhh... This is new!!

I have been looking at it like a method where arguments can be assigned and then processed into the workbook or VBA.

I will go and play with this and see what it does.

TX

[HansV]

See Built-In Dialog Box Argument Lists [Excel 2003 VBA Language Reference] or Built-In Dialog Box Argument Lists [Excel 2007 Developer Reference].

For example, you'll see that the xlDialogSendMail dialog has three arguments: recipients, subject, return_receipt. These are Arg1, Arg2 and Arg3, respectively.
So if you want to display the dialog with the subject already filled in, you can use

Application.Dialogs(xlDialogSendMail).Show Arg2:="Here is the report for March 2010"

[Rudi]

Thanks for that example Hans.

This has clarified a lot for me about the application.dialogs feature.

Cheers!!!