Setting dataseries-colors with VBA in XL2007

  • Thread starter Thread starter Holger Gerths
  • Start date Start date
H

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.
 
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.
 
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.
 
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
 
Back
Top