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

this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

On Nov 24, 5:06 pm, wrote:
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

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

On Nov 24, 5:22 pm, "WhytheQ" wrote:
this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

On Nov 24, 5:06 pm, wrote:

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

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



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

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


wrote in message
ps.com...
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

On Nov 24, 5:22 pm, "WhytheQ" wrote:
this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

On Nov 24, 5:06 pm, wrote:

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

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

On Nov 25, 1:48 am, "Robin Hammond"
wrote:
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 Hammondwww.enhanceddatasystems.com

wrote in glegroups.com...

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

On Nov 24, 5:22 pm, "WhytheQ" wrote:
this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

On Nov 24, 5:06 pm, wrote:

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

Hi,

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

Cheers
Andy

wrote:
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

On Nov 25, 1:48 am, "Robin Hammond"
wrote:

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 Hammondwww.enhanceddatasystems.com

wrote in glegroups.com...


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

On Nov 24, 5:22 pm, "WhytheQ" wrote:

this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

On Nov 24, 5:06 pm, wrote:

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

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

On Nov 25, 11:15 am, Andy Pope wrote:
Hi,

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

Cheers
Andy

wrote:
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

On Nov 25, 1:48 am, "Robin Hammond"
wrote:

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 Hammondwww.enhanceddatasystems.com

wrote in glegroups.com...

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

On Nov 24, 5:22 pm, "WhytheQ" wrote:

this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

On Nov 24, 5:06 pm, wrote:

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

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


wrote in message
ups.com...
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

On Nov 25, 11:15 am, Andy Pope wrote:
Hi,

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

Cheers
Andy

wrote:
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

On Nov 25, 1:48 am, "Robin Hammond"
wrote:

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 Hammondwww.enhanceddatasystems.com

wrote in
ooglegroups.com...

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

On Nov 24, 5:22 pm, "WhytheQ" wrote:

this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

On Nov 24, 5:06 pm, wrote:

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

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

wrote in message
ups.com...
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

On Nov 25, 11:15 am, Andy Pope wrote:
Hi,

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

Cheers
Andy

wrote:
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

On Nov 25, 1:48 am, "Robin Hammond"
wrote:

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 Hammondwww.enhanceddatasystems.com

wrote in
glegroups.com...

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

On Nov 24, 5:22 pm, "WhytheQ" wrote:

this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

On Nov 24, 5:06 pm, wrote:

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

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

On Nov 25, 3:06 pm, "Peter T" <peter_t@discussions wrote:
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

wrote in oglegroups.com...

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

On Nov 25, 11:15 am, Andy Pope wrote:
Hi,

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



Cheers
Andy

wrote:
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

On Nov 25, 1:48 am, "Robin Hammond"
wrote:

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 Hammondwww.enhanceddatasystems.com

wrote ooglegroups.com...



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

On Nov 24, 5:22 pm, "WhytheQ" wrote:

this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

On Nov 24, 5:06 pm, wrote:

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

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


wrote in message
oups.com...
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

On Nov 25, 3:06 pm, "Peter T" <peter_t@discussions wrote:
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

wrote in
oglegroups.com...

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

On Nov 25, 11:15 am, Andy Pope wrote:
Hi,

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



Cheers
Andy

wrote:
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

On Nov 25, 1:48 am, "Robin Hammond"
wrote:

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 Hammondwww.enhanceddatasystems.com

wrote
ooglegroups.com...



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

On Nov 24, 5:22 pm, "WhytheQ" wrote:

this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

On Nov 24, 5:06 pm, wrote:

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

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

wrote in message
ps.com...
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

On Nov 24, 5:22 pm, "WhytheQ" wrote:
this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

On Nov 24, 5:06 pm, wrote:

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

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, he

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

On Nov 27, 3:28 am, "NickHK" wrote:
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

wrote in glegroups.com...

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

On Nov 24, 5:22 pm, "WhytheQ" wrote:
this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

On Nov 24, 5:06 pm, wrote:

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

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

wrote in message
ups.com...
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, he

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

On Nov 27, 3:28 am, "NickHK" wrote:
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

wrote in
glegroups.com...

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

On Nov 24, 5:22 pm, "WhytheQ" wrote:
this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

On Nov 24, 5:06 pm, wrote:

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

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

"Peter T" <peter_t@discussions wrote in message
...
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

wrote in message
ups.com...
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, he

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

On Nov 27, 3:28 am, "NickHK" wrote:
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

wrote in
glegroups.com...

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

On Nov 24, 5:22 pm, "WhytheQ" wrote:
this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

On Nov 24, 5:06 pm, wrote:

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

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).

--
Robin Hammond
www.enhanceddatasystems.com



wrote in message
ups.com...
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, he

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

On Nov 27, 3:28 am, "NickHK" wrote:
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

wrote in
glegroups.com...

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

On Nov 24, 5:22 pm, "WhytheQ" wrote:
this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

On Nov 24, 5:06 pm, wrote:

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

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(myColo rIndex)

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

"Robin Hammond" wrote in
message ...
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).

--
Robin Hammond
www.enhanceddatasystems.com



wrote in message
ups.com...
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, he

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

On Nov 27, 3:28 am, "NickHK" wrote:
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

wrote in
glegroups.com...

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

On Nov 24, 5:22 pm, "WhytheQ" wrote:
this is close enough isn't it:

Application.Dialogs(xlDialogPatterns).Show

Rgds
J

On Nov 24, 5:06 pm, wrote:

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

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

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

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

Glad you like it but ...

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

Don't hold your breath!

The point is there's no way of knowing what user has done, if anything,
without checking cells for changes, but what and when. If the form is loaded
as Modal user can't even click the normal just-for-fun 'menus'. If as
modeless user could have selected anything in the mean time or dismissed
those menus.

The only half practical way of doing what as I understand you want is along
the lines I suggested with xlDialogPatterns. Ie just before showing it trap
the current selection & visible range, with screenupdating false select some
single cell, store its colour format, show the dialog, if it returns true
read the new format X, restore that cell format & selection as original.
Work with X.

Regards,
Peter T


wrote in message
ups.com...
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

Yes, that's what I've found. But to me it looks better than showing the
xldialogPatterns, because it's more or less just a list of colours,
whereas the dialog says "Patterns" and "Format Cells" and so on, which
might suggest the procedure is going to do something it's not (i.e.
what it's supposed to do). So I don't know, I guess it's just a
preference - to me the commandbar popup (e.g. Fill Color) would be less
confusing. Especially without the titlebar (see below).

Only thing is, I can't select anything in the popup until I close my
original form. At the moment I've got a button which, when clicked,
stores the current colour of a particular cell, launches the Fill Color
commandbar popup, and is supposed to then read in the cell colour after
the popup is used, then reverting back to the stored colour while
taking the chosen colour as the desired value. But I can't shift the
focus to the popup. Any ideas? Do I need to run a procedure outside my
userform, and close the userform while it runs?

And ideally I'd like to have the popup appear as a popup attached to a
button, like the toolbar buttons Font Color and Fill Color. How can I
set the popup to attach to my control in that way? Presumably that
would remove the titlebar from the popup, like when it is attached to
the toolbar buttons. That would look even better.

Maybe I'm just being fussy, but to me this would be the ideal colour
picker for a userform, and is the kind of thing a lot of people could
use in their own projects.

Thanks for your help.

Chris

And ideally I'd like to have the popup appear as a popup attached to a
button, like the toolbar buttons Font Color and Fill Color. How can I
set the popup to attach to my control in that way? Presumably that
would remove the titlebar from the popup, like when it is attached to
the toolbar buttons. That would look even better.

Conceivably there might be some devilishly cunning way with API's but apart
from that I don't see how. At least I assume you mean to get the normal
dropdown palette attached to your userform control and directly trap what
user clicks on it

Maybe I'm just being fussy, but to me this would be the ideal colour
picker for a userform, and is the kind of thing a lot of people could
use in their own projects.

Only way is with your own colour picker - circle back to my first post in
this thread!

Regards,
Peter T

wrote in message
oups.com...
Yes, that's what I've found. But to me it looks better than showing the
xldialogPatterns, because it's more or less just a list of colours,
whereas the dialog says "Patterns" and "Format Cells" and so on, which
might suggest the procedure is going to do something it's not (i.e.
what it's supposed to do). So I don't know, I guess it's just a
preference - to me the commandbar popup (e.g. Fill Color) would be less
confusing. Especially without the titlebar (see below).

Only thing is, I can't select anything in the popup until I close my
original form. At the moment I've got a button which, when clicked,
stores the current colour of a particular cell, launches the Fill Color
commandbar popup, and is supposed to then read in the cell colour after
the popup is used, then reverting back to the stored colour while
taking the chosen colour as the desired value. But I can't shift the
focus to the popup. Any ideas? Do I need to run a procedure outside my
userform, and close the userform while it runs?

And ideally I'd like to have the popup appear as a popup attached to a
button, like the toolbar buttons Font Color and Fill Color. How can I
set the popup to attach to my control in that way? Presumably that
would remove the titlebar from the popup, like when it is attached to
the toolbar buttons. That would look even better.

Maybe I'm just being fussy, but to me this would be the ideal colour
picker for a userform, and is the kind of thing a lot of people could
use in their own projects.

Thanks for your help.

Chris

Thanks Peter. That makes sense. But how about the question of shifting
focus to the popup palette so I can use it like xldialogpatterns?

Chris

By "popup palette" we are talking about Excel's normal dropdown colour
palettes on the formatting toolbar and not dialogs - right? These are a
strange type of Commandbar.

As I've been trying to explain it ain't going to work, at least I can't
imagine how.

If the Form is shown modeless there's no point, user has total control but
you won't know what he has done. If shown modal there is a way of switching
to modeless but then you are back to the same problem.

I think you are chasing a rainbow !

Regards,
Peter T

wrote in message
ups.com...
Thanks Peter. That makes sense. But how about the question of shifting
focus to the popup palette so I can use it like xldialogpatterns?

Chris

Sorry, Peter, I'm not following this. Yes, I mean the normal dropdown
colour palettes on the formatting toolbar. Not dialogs. Your earlier
code shows how we can access the palettes (although I've altered it
since - just a simpler version to get a single palette). The palette is
shown. But then my userform takes focus again. I think I understand
part of what you were saying - that if I just have the palette showing
then the user can do what he likes, including using the workbook, and
obviously I don't want that to be possible. But at the moment my
userform prevents that happening, but it also prevents the palette
being used.

Can I not keep my userform modal but access the palette?

I have a working solution now with xlDialogPalettes, and I want to
thank everybody for their help with that. This would just be a bit
nicer.

Cheers,

Chris

Sorry, Peter, I'm not following this.

What are you not following

The palette is
shown. But then my userform takes focus again.

The form never looses focus

Can I not keep my userform modal but access the palette?

No !

I have a working solution now with xlDialogPalettes,

What is 'xlDialogPalettes', I don't have that one.

Regards,
Peter T

wrote in message
oups.com...
Sorry, Peter, I'm not following this. Yes, I mean the normal dropdown
colour palettes on the formatting toolbar. Not dialogs. Your earlier
code shows how we can access the palettes (although I've altered it
since - just a simpler version to get a single palette). The palette is
shown. But then my userform takes focus again. I think I understand
part of what you were saying - that if I just have the palette showing
then the user can do what he likes, including using the workbook, and
obviously I don't want that to be possible. But at the moment my
userform prevents that happening, but it also prevents the palette
being used.

Can I not keep my userform modal but access the palette?

I have a working solution now with xlDialogPalettes, and I want to
thank everybody for their help with that. This would just be a bit
nicer.

Cheers,

Chris

Hi Peter.


I have a working solution now with xlDialogPalettes,What is 'xlDialogPalettes', I don't have that one.


That was a typo! I meant xlDialogPatterns. I can live with my current
solution for the time being. Thanks for your help.

Chris