Solved: Return Excel Dialogs selections

[ALe]

Hi everybody!

Do you know a way to use excel biult-in dialogs in order to store the selected option in a variable?

For example something like this:
[VBA]
IndexColor=application.dialogs(84).show
[/VBA]

in order to get the colour selected

Thank you

[Bob Phillips]

[vba]

'-----------------------------*------------------------------*----------------
Function GetColorindex(Optional Text As Boolean = False) As Long
'-----------------------------*------------------------------*----------------
Dim rngCurr As Range

Set rngCurr = Selection
Application.ScreenUpdating = False
Range("IV1").Select
Application.Dialogs(xlDialogPatterns).Show
GetColorindex = ActiveCell.Interior.ColorIndex
If GetColorindex = xlColorIndexAutomatic And Not Text Then
GetColorindex = xlColorIndexNone
End If
ActiveCell.Interior.ColorIndex = xlColorIndexAutomatic
rngCurr.Select
Set rngCurr = ActiveSheet.UsedRange
Application.ScreenUpdating = True
End Function
[/vba]

[ALe]

If I'm not mistaken you get the colour by reading the colour of the cell "IV1". Is there any way to get it directly without reading it from the cell?

[Marcster]

Hi Ale,

I've been looking into Excel's built-in dialogs
to see if I can manipulate them or find a
quicker way of doing things.
Typing:
Built-In Dialog Box Argument Lists
into help brings up a page of all Excel's
bulit-in dialogs you can use, including
their arguments you can use.
Or http://msdn.microsoft.com/library/de...HV05199604.asp

For example,To open a mail message,
placing the active workbook as an attachment,
setting the subject line and the e-mail address
to send it to, only needs these lines of code:
[vba]With Application.Dialogs(xlDialogSendMail)
.Show "someone@somewhere.com", "Subject Line"
End With[/vba]
What is dialogs(84)?. Think it's xlDialogPatterns?.

The arguments for xlDialogPatterns are as follows:
xlDialogPatterns apattern, afore, aback, newui

Going back to your original question,
I'm not sure you can capture what the user has selected in the dialog.
I maybe wrong.

I think you can only set which options are to be selected etc and
not know what the user has chosen in the dialog.

I'll have a play around...

Marcster.

[Marcster]

The only thing the dialog returns is True or False:
[VBA]Dim x
With Selection
x = Application.Dialogs(xlDialogPatterns).Show
End With
MsgBox x 'returns True if OK button is pressed, False if Cancel is pressed.[/VBA]

Marcster.

[Bob Phillips]

If I'm not mistaken you get the colour by reading the colour of the cell "IV1". Is there any way to get it directly without reading it from the cell?

Don't think so.

[ALe]

As far as i can imagine, the built-in functions can be used as subs and not as functions (some of them return True or False as it is said in the on-line help and that's it).

Dunno...I'll continue to look around

[ALe]

Quoting from Tom Ogilvy:
"The dialog is the same as selecting this from the menu - it performs a
specific action which is to color the cells that are selected. It isn't a
general purpose dialog that you can use as a color picker. So the answer is
no as far as any builtin in capability you can employ to do this."

However, If you color the selection, you can get that information from the
selection...