PDA

View Full Version : Solved: Return Excel Dialogs selections



ALe
04-19-2006, 04:48 AM
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:

IndexColor=application.dialogs(84).show


in order to get the colour selected

Thank you

Bob Phillips
04-19-2006, 04:52 AM
'-----------------------------*------------------------------*----------------
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

ALe
04-19-2006, 06:07 AM
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
04-19-2006, 08:18 AM
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/default.asp?url=/library/en-us/vbaxl11/html/xlmscDialogArgLists1_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:
With Application.Dialogs(xlDialogSendMail)
.Show "someone@somewhere.com", "Subject Line"
End With
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
04-19-2006, 08:33 AM
The only thing the dialog returns is True or False:
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.

Marcster.

Bob Phillips
04-19-2006, 08:33 AM
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
04-19-2006, 09:20 AM
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
04-19-2006, 09:35 AM
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...