VBA color picker - copy of Font dialog combo box

[Peter T]

"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

 

[csugden]

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

 

[Peter T]

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

 

[csugden]

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

 

[Peter T]

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

 

[csugden]

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

 

[Peter T]

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

 

[csugden]

Hi Peter.

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

Chris