Setting active color of Fill Color and/or Font color tool

[mithrandiir]

After banging my head against my keyboard and still not solving my problem, i thought I'd see if anyone from the forum here has any other ideas. What i'm trying to do is programmatically set the active color of the Font Color tool in the Formatting bar. Excel defaults to Red but what i'm trying to do it write a VBA script to set it to default to some other color of my choice (we'll just say blue for now) so far I can bring up the dialog by using this line of code:

Application.CommandBars("Font Color").Visible = True

I also found that under the Application.CommandBars("Font Color") dialog there are 3 CommandBarControls; one of which (the 3rd one) is labeled "Color" but I can't figure out how to access all the colors listed on the palette.

It seems like there should be some CommandBarControl under that with the caption "Blue" that I can run the .Execute funtion to set the active Font Color.

I hope that all made sense...

Any ideas?

Thanks!

[OBP]

I am not sure if this applies but Windows applications uses palette "values" for the colour of Borders/Font/Interior, so you need to know the palette value for the colour that you want. For instance black is 1, white is two. Look in the VBA Editor help for "ColorIndex Property" to see the list.

[Bob Phillips]

Here is some code that runs a colour picker. To demonstrate it , type
?ReturnColorindex() in the immediate window, and you will be returned the
number of the colour that you choose.

[VBA]
Function ReturnColorindex() As Long
Dim rngCurr As Range
Set rngCurr = Selection
Application.ScreenUpdating = False
Range("IV1").Select
Application.Dialogs(xlDialogPatterns).Show
ReturnColorindex = ActiveCell.Interior.ColorIndex
ActiveCell.Interior.ColorIndex = xlColorIndexAutomatic
rngCurr.Select
End Function
[/VBA]


Note, you cannot call this function as a UDF as it updates another cell in
the Workbook.