Working with most recently used colors

[Mel]

Hi. In Excel 2003, I want to build a macro that will add a color to
the CUSTOM or "Recently Used Colors" area in the tools for Fill/Line
Color for autoshapes (not cell fill colors). I want the code to do
this without actually having to use that color first. The code would
contain RGB numbers.

Basically, when I click a custom button on the toolbar it would
emulate having used that color and placed it as the first of 8 colors
in that tool area that shows recently used colors. When I click
another like button but coded with different RGB, it places that color
in the first spot and shifts the others to the right like it normally
does when using colors.

Does anyone have any code for that or ideas? Thanks.

-Melina

 

[Peter T]

I never say anything is impossible in Excel but what you ask is going to be
difficult! It does not appear possible even to see what colours are included
in the control yet alone change them. In a new sheet add a shape, format
fill with a custom colour and run the following -

Sub abc()
Dim cb As CommandBar, ctl As CommandBarControl
ActiveSheet.DrawingObjects(1).Select
Set cb = CommandBars("Fill Color")
cb.Visible = True
Set ctl = cb.Controls("Recently Used Colors")
Stop
' look at ctl in locals, press Alt-v, s
End Sub

I can't see anything obvious to work with.

The other approach might be to get the control to change itself. The
"Recently used colours" as applied manually are stored in the Workbook, not
sure where or how. But if you can find that list and change it, I imagine
the control will do what you want.

Regards,
Peter T

 

[Mel]

It can be done in PPT, so I thought maybe in Excel too?

In PPT it's using ActivePresentation.ExtraColors.Add RGB(125, 68, 25),
but I think I explored ExtraColors in Excel and came up short.

-Melina