Setting dataseries-colors with VBA in XL2007

[Holger Gerths]

Dear Group,

I want to set the color of some dataseries in a chart in XL2007 via VBA.
I don't want to simply set RGB-values, but a theme color.

Why VBA?
I am creating a tool that helps me to migrate old 2003 workbooks into new
theme-color system.
I am handling with about 1.000 series to be converted.

Why theme-colors?
I want to be able to switch between different color-themes and

With the following simplified code-fragment I try to set marker's background
and foreground color of an xy-scatter.
THIS IS IN A WORKBOOK CREATED WITH XL2003 AND SAVED AS A 2007-FILE.

------------------------------------------------------------------------
Dim s As Series
...
Set s = ...
...
s.Format.Fill.BackColor.ObjectThemeColor = 5
s.Format.Fill.BackColor.TintAndShade = 0.6
s.Format.Fill.ForeColor.ObjectThemeColor = 9
s.Format.Fill.ForeColor.TintAndShade = 0.15

------------------------------------------------------------------------

If I debug, TintAndShade always remains 0.
ObjectThemeColor always remains what it is (0 or -2).
Am I thinking the wrong way?
Because Macro-recording in XL2007 does not work with charts, I did in XL2010
and here I got the idea to use ObjectThemeColor and TintAndShade.
What is wrong?

Thanks in advance,
Holger.

 

[Herbert Seidenberg]

Excel 2007 Colors
http://c0444202.cdn.cloudfiles.rackspacecloud.com/10_31_09a.xlsm

 

[Holger Gerths]

Hi Herbert,

thanks for your example, but you when setting colors in the chart, you are
working with the old (<=2003) palette
(Excel-Options - Save - ...previous versions of Excel):
.Points(i).MarkerBackgroundColorIndex = sb(i, 1)
.Points(i).MarkerForegroundColorIndex = sf(i, 1)

An object with a color set this way it is not affected when changing (new)
color theme.
I am looking for a code that is setting color in the same way as Excel 2007
color-picker does.

Thanks in advance,
Holger.

 

[Herbert Seidenberg]

Excel 2007
A point has no association with a theme.
Trying to access the ThemeColor property will
result in an error.
A work-around is to use the RGB color of
a theme colored cell or shape, as demonstrated by
the line color macro.
Also see ThemePoint() macro.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/10_31_09a.xlsm

 

[Holger Gerths]

Hi Herbert,

sorry, but a point or a series has an association with a theme.
I can set a dataseries-color with XL2007 color-picker and then activate
another color-theme.
What I see ist that the dataseries-color changes according to the new
theme-color.
Do you mean that it is impossible to associate a dataseries-color with a
theme via VBA?

Thanks for your patience,
best regards, Holger.

 

[Jon Peltier]

I've found this to be unreliable. I haven't done enough with it to
elaborate, other than your experience is not unlike my own.

- Jon