xlDialogColorPalette
Jeremy
I am trying to add a color palette to a macro, but I've never used it
in the past. Could anyone send me a short sample of how it can be
implemented to return color values at user's selection?
Thank you all in advance,
Jeremy
Tom Ogilvy
What do you mean by adding a color palette to a macro - you mean
application.Dialogs(xlDialogColorPalette).Show
That is pretty much it. This allows the user to modify the pallete - other
than that it doesn't do anything. It is not a general purpose dialog that
you can put up and extract data from. Once shown, Excel does its thing and
returns only true or false based on whether the user hit OK or Cancel.
Regards,
Tom Ogilvy
MVP Excel
Jeremy <jmo...@iprolink.ch> wrote in message
news:eCNiOXnf6fyFsp...@4ax.com...
Tom Ogilvy
have to design your own (or see below). All the built in dialogs do what
they are designed to do - they don't return any values.
Jim Rech previously suggested to a similar question that the person could
put up the cell color dialog and then pick up the color of the cell (you
could make an out of the way cell the active cell. This dialog is:
Application.Dialogs(xlDialogPatterns).Show
John Walkenbach has built one using userforms which you can download at:
http://www.j-walk.com/ss/excel/tips/tip49.htm
Regards,
Tom Ogilvy
MVP Excel
Jeremy <jmo...@iprolink.ch> wrote in message
news:ImFiOcIh2p94keTQLLRMZFrkvr=L...@4ax.com...
> Thanks for answering Tom,
>
> What I'm trying to do is to include on a form or as a popup, a color
> palette that would return the color value, of the chosen color, and
> then apply it to whatever. I thought that the xlDialogColorPalette
> could return the value of the chosen color, but apparently not. Do you
> have any suggestions?
>
> Thanks in advance,
>
> Jeremy
Jeremy
Stratos Malasiotis
Another way to get a color from a color palette is to use the respective
windows common control.
To do that assign the following to a button:
-----------------------------------------------------
Private Sub CommandButton1_Click()
Module1.ColorCell
End Sub
-----------------------------------------------------
and in a standard module add:
-----------------------------------------------------
Private Declare Function ChooseColor Lib "comdlg32.dll" Alias
"ChooseColorA" (pChoosecolor As ChooseColor) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA"
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Type ChooseColor
lStructSize As Long
hwndOwner As Long
hInstance As Long
rgbResult As Long
lpCustColors As String
flags As Long
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Private Function ShowColor() As Long
Dim ChooseColorStructure As ChooseColor
Dim Custcolor(16) As Long
Dim lReturn As Long
ChooseColorStructure.lStructSize = Len(ChooseColorStructure)
ChooseColorStructure.hwndOwner = FindWindow("XLMAIN",
Application.Caption)
ChooseColorStructure.hInstance = 0
ChooseColorStructure.lpCustColors = StrConv(CustomColors,
vbUnicode)
ChooseColorStructure.flags = 0
If ChooseColor(ChooseColorStructure) <> 0 Then
ShowColor = ChooseColorStructure.rgbResult
CustomColors = StrConv(ChooseColorStructure.lpCustColors,
vbFromUnicode)
Else
ShowColor = -1
End If
End Function
Sub ColorCell()
ActiveCell.Interior.Color = ShowColor
End Sub
-----------------------------------------------------
the ShowColor function (from http://kpdteam.tripod.com/index.html) will
return the selected long color and will paint the active cell. However,
Excel does not have all the millions of colors that can be selected in
that pallete and it makes compromises.
To overcome this problem you'll have to assign a custom color pallete to
the dialog (see at : http://www.vbapi.com/ref/c/choosecolor.html for an
example).
HTH
Stratos
Lyle Green
You can simulate one using command buttons which works perfectly well.
John Walkenbach originated the technique and demonstrates it in his
book. A demo file is also available on his web-site. I use this
technique in many of the in-house applications I develope and it always
works create. You can also extend the technique to simulate other Excel
dialogues which which are not available as ActiveX controls (the border
select dialogue was particularly challenging.
In article <#aU1mle5$GA....@cppssbbsa02.microsoft.com>,
"Tom Ogilvy" <twog...@email.msn.com> wrote:
>
> What do you mean by adding a color palette to a macro - you mean
> application.Dialogs(xlDialogColorPalette).Show
>
> That is pretty much it. This allows the user to modify the pallete -
other
> than that it doesn't do anything. It is not a general purpose dialog
that
> you can put up and extract data from. Once shown, Excel does its
thing and
> returns only true or false based on whether the user hit OK or Cancel.
>
> Regards,
> Tom Ogilvy
> MVP Excel
>
> Jeremy <jmo...@iprolink.ch> wrote in message
> news:eCNiOXnf6fyFsp...@4ax.com...
> > Hi guys,
> >
> > I am trying to add a color palette to a macro, but I've never used
it
> > in the past. Could anyone send me a short sample of how it can be
> > implemented to return color values at user's selection?
> >
> > Thank you all in advance,
> >
> > Jeremy
> >
> > jmo...@iprolink.ch
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Lyle Green
If you would like a third-party ActiveX Colour Control ComboBox there
is one available at: http//members.tripod.com. The registration fee is
$25 (US I assume)
Regards
Lyle Green
In article <#4j5mtg5$GA....@cppssbbsa02.microsoft.com>,
"Tom Ogilvy" <twog...@email.msn.com> wrote:
> Unless you can come up with a third party control that does it, you
would
> have to design your own (or see below). All the built in dialogs do
what
> they are designed to do - they don't return any values.
>
> Jim Rech previously suggested to a similar question that the person
could
> put up the cell color dialog and then pick up the color of the cell
(you
> could make an out of the way cell the active cell. This dialog is:
>
> Application.Dialogs(xlDialogPatterns).Show
>
> John Walkenbach has built one using userforms which you can download
at:
> http://www.j-walk.com/ss/excel/tips/tip49.htm
>
> Regards,
> Tom Ogilvy
> MVP Excel
>
> Jeremy <jmo...@iprolink.ch> wrote in message
> news:ImFiOcIh2p94keTQLLRMZFrkvr=L...@4ax.com...
> > Thanks for answering Tom,
> >
> > What I'm trying to do is to include on a form or as a popup, a color
> > palette that would return the color value, of the chosen color, and
> > then apply it to whatever. I thought that the xlDialogColorPalette
> > could return the value of the chosen color, but apparently not. Do
you
> > have any suggestions?
> >
> > Thanks in advance,
> >
> > Jeremy
> >
> > >
> > >What do you mean by adding a color palette to a macro - you mean
> > >application.Dialogs(xlDialogColorPalette).Show
> > >
> > >That is pretty much it. This allows the user to modify the
pallete -
> other
> > >than that it doesn't do anything. It is not a general purpose
dialog
> that
> > >you can put up and extract data from. Once shown, Excel does its
thing
> and
> > >returns only true or false based on whether the user hit OK or
Cancel.
> > >
> > >Regards,
> > >Tom Ogilvy
> > >MVP Excel
> >
>
>
--
Jeremy
Jeremy