VBA color picker - copy of Font dialog combo box

[csugden]

Hi,

I've had a look at a lot of color picking code for userforms, but I was
wondering if there was any way of just producing the same combo box
that can be found in Format-->Cells-->Font (I'm using Excel).

I've looked at various application.dialogs, but they would confuse my
users - all I need is the colour, and the way it is done in the Font
page seems ideal.

Either an inbuilt solution (i.e. something within excel that I can
reference, like a dialog) or a copy would be fine. Anyone seen anything
like this?

Thanks very much,

Chris

 

[WhytheQ]

this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

 

[csugden]

Thanks J. That's close, but not close enough really. I'd rather code up
my own colour picker than use a dialog that appears to have a function
(i.e. colouring cells) which is not what my users would be choosing the
colour for. Just wondering if there was some way of accessing the one
used in the Font dialog, as that's perfect. All I've found so far are
copies of that written in C, which I'm afraid I don't know how to
implement.

Chris

 

[Jim Cone]

John Walkenbach has a ready made color picker here ...
http://www.j-walk.com/ss/excel/tips/tip49.htm
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


<[email protected]>
wrote in message
Hi,
I've had a look at a lot of color picking code for userforms, but I was
wondering if there was any way of just producing the same combo box
that can be found in Format-->Cells-->Font (I'm using Excel).
I've looked at various application.dialogs, but they would confuse my
users - all I need is the colour, and the way it is done in the Font
page seems ideal.
Either an inbuilt solution (i.e. something within excel that I can
reference, like a dialog) or a copy would be fine. Anyone seen anything
like this?
Thanks very much,
Chris

 

[Robin Hammond]

Chris,

Using the Microsoft Common Dialog controls you can get something close. The
drawback is that they need to be installed on target machines.

e.g.
Sub GetColour
CommonDialog1.ShowColor
If Err Then Exit Sub
msgbox CommonDialog1.Color
end sub

Robin Hammond
www.enhanceddatasystems.com

 

[csugden]

Thanks for both of those suggestions. I had seen John W's colour picker
and that's probably what I would call "Plan B". It'd do the job, but
not in the way I'd like. (I know, I'm fussy!)

As for the Common Dialogs, that's something I didn't know about.
Unfortunately this is going to be a downloadable workbook, so I need to
make sure it is widely supported. But it's definitely an avenue for me
to look down. Thanks very much.

Cheers,

Chris

 

[Andy Pope]

Hi,

You might be able to re work this dropdown listbox example.
http://www.andypope.info/vba/colourdropdown.htm

Cheers
Andy

 

[csugden]

Thanks Andy. If I end up having to write my own, that will definitely
help.

I've been looking through Common Dialog resources, and was wondering if
anyone knew the answer to this: if I am distributing an excel file
(with vba code), does that mean that the required .dll file (required
for the Common Dialogs) will be on the user's computer, because they
must have Office installed? In other words, is the warning to
developers that the .dll must be distributed with the program only
relevant for those making standalone VB applications, because the user
might not have Office and hence not the .dll?

It's just a hunch that the common dialogs would automatically be
available wherever Office is installed, but it seems probable (given
the name!).

If I can be sure the common dialogs will work whenever a copy of Excel
is being used, then that fits the bill perfectly. Anyone have an answer
to this question?

Cheers,

Chris

 

[Robin Hammond]

Chris,

Unfortunately, I don't think you can safely assume that. It's a long time
since I checked up on this but I remember including the common controls in
the install package for my XspandXL add-in for a reason.

Robin Hammond
www.enhanceddatasystems.com

 

[Peter T]

Why not indeed have a go at writing your own. I have a soft spot for
j-walk's color picker which can be adapted to look very similar to Excel's
drop down palette, colours correctly arranged and with all colour controls
added at runtime (ie small *frx size). It's minimal code to do that.

However, and another subject, if it's to be used to apply colour format to
any type of selection, and to whatever fill font border, will involve a
tremendous amount of code (I've done it!).

Regards,
Peter T

 

[csugden]

It's looking like I will be writing my own after all. Ah well, learning
by doing. I'll use a few examples to work from, including j-walk's (as
soon as I get to a windows computer so I can run his .exe downloads!)

I was a little confused by your message, Peter. If adjusting j-walk's
colour picker won't take much coding, what is it that will involve a
lot of code? It is to apply format, yes, in that my program will store
a background colour for cells and two different foreground colours for
different kinds of output.

Cheers,

Chris

 

[Peter T]

I didn't mean to confuse <g>

The colour picker is simple enough, I can probably put a demo together along
the lines I mentioned if that's what you're looking for. If you only want to
cater for cell fills the code would be pretty straightforward (though still
a bit to make it all reliable).

Regards,
Peter T

 

[NickHK]

There are arguments to the .Show method, according to help :
Application.Dialogs(xlDialogPatterns).Show 'lauto, lstyle, lcolor, lwt,
hwidth, hlength, htype

You would need some research to understand what each is expecting as a value
and its consequence.

NickHK

 

[csugden]

Peter, thank you for your advice and your offer to help me construct my
own color picker. However, if there is the option to use an inbuilt
functionality, I will probably choose that to reduce overall file size.


And Nick seems to be on to something with these arguments. I found that
Show could handle a combination of 30 boolean arguments, but in a way
not entirely predictable to me! However, I have also found (in a list
of other dialogs), the following:

xlDialogEditColor color_num, red_value, green_value, blue_value

This brings up a dialog used (I think) for creating your own colour,
but I think it serves my purposes well enough, even if it doesn't look
like the standard color picker. It also takes a list of booleans,
however integers can be substituted. It seems to determine the default
value in the dialog. I'm sure trial and error will reveal to me what
may be obvious to anyone who understands RGB values. I'll have a play
around.

Thank you everyone for your help. If anyone is interested, here:

http://www.code-vb.com/fragments/Excel_Dialogs.htm

is a list of many other inbuilt dialogs, from which I found the one
that suits me.

Cheers,

Chris

 

[Peter T]

Hi Chris,

Intellisense might suggest 30 arguments are available but the number depends
on the particular dialog and possibly the current selection (cell/shape
etc). These optional arguments allow you to set the dialog to show something
different to the default, namely the current cell format.

You could, say, show the dialog always ready with red regardless of the
current cell colour, eg
bRes = Application.Dialogs(xlDialogPatterns).Show(1, , 6)

xlDialogPatterns returns true/false depending on whether user clicked
OK/Cancel. The cell format will be applied immediately after click OK. IOW
you can only trap the colorindex by reading the cell format after applied.

If this serves your purpose the built in dialog is no doubt the simplest
solution.

BTW, all the dialogs & arg's in the link you posted is copied from VBA's
help.

Just for fun -

Sub ShowPickers()
Dim lt As Long, tp As Long
Dim cb As CommandBar
Dim va
va = Array("Font Color", "Fill Color", "Pattern")
lt = 100: tp = 30
For i = 0 To 2
Set cb = Application.CommandBars(va(i))
With cb
.Left = lt
.Top = tp
.Visible = True ' false to hide like normal
lt = lt + .Width
End With
Next
End Sub

Regards,
Peter T

 

[Peter T]

typo -

You could, say, show the dialog always ready with red regardless of the
current cell colour, eg
bRes = Application.Dialogs(xlDialogPatterns).Show(1, , 6)

6 is yellow, red is 3 in a default palette

Peter T

 

[Robin Hammond]

Chris,

Not to throw a spanner in the works, watch out on those colours. To actually
apply them reliably you will have to add the custom colour value to the
Excel colour palette. If not, Excel will just pick what it believes is the
closest match (or so I understand).

 

[Peter T]

Hi Robin,

I don't think that should be a problem with the xlDialogEditColor

dim myColorIndex as long
myColorIndex = 3 ' 1 to 56
bResult = Application.Dialogs(xlDialogEditColor).Show(myColorIndex)

This should customize the colour.

I'm still not quite sure of the OP's overall objective. The xlDialogPatterns
dialog will apply a colorindex to the selection's interior if that's what's
needed. To implement would mean something like a button on the form that
says -

"Click me to pop up a colour-picker from which you can choose a colour and
apply to the current selection"

(though code might temporarily switch selection to a hidden cell, pick up
the new format, clear it, and apply same elsewhere).

This may well serve purpose but not same as having own colour picker on the
form.

Applying an RGB colour as a cell format would, as you say, be converted to
the nearest match in the palette then that colorindex applied.

Regards,
Peter T

 

[csugden]

Hiya,

Thanks for these thoughts. I think we're making progress! Maybe I ought
to clarify my objectives.

Yes, the colours picked will be to colour the sheet background and
text. No, it is not just to alter the currently selected cell. The idea
is to store a foreground colour and a background colour, which other
procedures can use later to colour a particular section of the sheet
with the chosen colours, when certain conditions are fulfilled.

I've managed that. What I want to do now is allow my users to select
those background and foreground colours. I don't really like the
xlDialogPalette dialog because when I pop it up it looks like it's
going to do its original function, which is not what the colour palette
is needed for.

The EditColors dialog looks good except it seems to change the actual
colour palette of Excel. What I want to do is just return the colour
chosen, without anything happening to the sheet. Any idea how to do
this?

Thanks very much,

Chris

 

[csugden]

Oh! My mistake! I hadn't seen the code Peter had posted. That's
fantastic!

"Just for fun" turns out to be exactly what I wanted.

Now all I need to do is figure out how to get the value selected before
one of you responds and tells me...

Chris