Passing parameters to built in Dialog boxes

[ttt123]

I would like to show the xlDialogOptionsListsAdd dialog and retrieve the name or index of the custom list that was selected.

I can show the dialog easily using:
Application.Dialogs(xlDialogOptionsListsAdd).Show

but I'm guessing that if I want to know what value the user selected, then I have to pass a parameter to Show().

This page says that the parameter is a string_array for xlDialogOptionsListsAdd: https://msdn.microsoft.com/EN-US/library/office/ff838781.aspx

there isn't much information regarding what the parameter is for. Is it the list to initialize the dialog by selecting a list by default when it is shown? Or is it, as I am hoping, the output of the dialog with the list that the user selected (instead of a return value, pass the output variable as a parameter)?

either way, I can't seem to get the code to run when I pass a parameter.
I have tried:

Dim list as Variant
Dim list2() as String
Dim list3 as Variant
list3 = Application.GetCustomListContents(1)

Application.Dialogs(xlDialogOptionsListsAdd).Show list
'Application.Dialogs(xlDialogOptionsListsAdd).Show list2
'Application.Dialogs(xlDialogOptionsListsAdd).Show list3

Does anyone know how to get this working?

 

[John_w]

I tried calling the Show method for xlDialogOptionsListsAdd as documented on that MSDN page (same as the VBA help) and had no success. The page shows 2 sets of arguments: "string_array" and "import_ref, by_row" and either of these should work:

    Application.Dialogs(xlDialogOptionsListsAdd).Show Array("cogs", "sprockets", "widgets", "gizmos")
    Application.Dialogs(xlDialogOptionsListsAdd).Show Range("A1:A5"), True

but both return "Show method of Dialog class failed".

This is in contrast to this code for xlDialogSendMail

Sub SendIt()
    'Application.Dialogs(xlDialogSendMail).Show arg1:="ask@mrexcel.com", arg2:="This goes in the subject line"
    Application.Dialogs(xlDialogSendMail).Show "ask@mrexcel.com", "This goes in the subject line"
End Sub

which works.

Therefore it seems that the Show method for xlDialogOptionsListsAdd has no parameters. Its return value is a Boolean which is True if the user clicks OK or False if the user clicks Cancel:

    Dim d As Dialog
    Dim response As Boolean
    Set d = Application.Dialogs(xlDialogOptionsListsAdd)
    response = d.Show

As well as not working according to the MSDN page and VBA help, I think the arguments for xlDialogOptionsListsAdd are referring to the 2 ways the Application.AddCustomList method can be called:

    Application.AddCustomList Array("cogs", "sprockets", "widgets", "gizmos")
    Application.AddCustomList Range("A1:A5"), True

In summary, I don't think you can determine which list the user selected. One possible workaround is to create a userform which replicates the dialogue and read the built-in dialogue lists using GetCustomListContents to populate the userform:

    Dim n As Long
    Dim list As Variant
    For n = 1 To Application.CustomListCount
        list = Application.GetCustomListContents(n)  'add list array items to userform
    Next

With the userform and appropriate code, you have more control over the user interaction and should be able to determine which list was selected.

 

[RoryA]

John is correct - you can't determine what options the user selected using the dialogs. The arguments you pass only determine how the dialog is presented to the user - they can't be used to return information in Excel.

 

[ttt123]

thank you for your help guys!

I opted to go the workaround route by having a form that shows a listbox of the existing custom lists and has an Add/Edit/Delete button that shows the built in dialog.

It was confusing because the MSDN documentation was incorrect and had so few details in it.

 

[tdh777]

John is correct - you can't determine what options the user selected using the dialogs. The arguments you pass only determine how the dialog is presented to the user - they can't be used to return information in Excel.

.... Which means (IMO) that having access to the built-in Excel Dialogs (Application.Dialogs) is worth basically squat !

Even the few that you might regularly use (Open, Save, Save-as etc) are called from a specific separate Application level Method, Application.FileDialog (because you can get a selection value BACK from that) :

Application.FileDialog([COLOR=#000000][FONT=&quot]msoFileDialogFilePicker[/FONT][/COLOR][COLOR=#000000][FONT=&quot])
[/FONT][/COLOR]Application.FileDialog([COLOR=#000000][FONT=&quot]msoFileDialogFolderPicker[/FONT][/COLOR][COLOR=#000000][FONT=&quot])
[/FONT][/COLOR]Application.FileDialog([COLOR=#000000][FONT=&quot]msoFileDialogOpen[/FONT][/COLOR][COLOR=#000000][FONT=&quot])
[/FONT][/COLOR]Application.FileDialog([COLOR=#000000][FONT=&quot]msoFileDialogSaveAs[/FONT][/COLOR][COLOR=#000000][FONT=&quot])[/FONT][/COLOR]

Almost all the built-in Dialogs are nigh-on useless to the VBA programmer - they are effectively slightly prettier versions of Msgboxes.

The values entered / changed by the operator are not stored anywhere - so you can't even interrogate other parts of Excel Object model after they have run to see what the user might have changed. Values the users enter in the Dialogs are DISCARDED.