Setting dataseries-colors with VBA in XL2007
in [Charts]
|
Prev: Can you change a horizontal bar chart to start at the right
Next: Charting Beginning Revenue and Changes Over a 12 year period
From: Holger Gerths on 26 Feb 2010 09:13 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.
From: Herbert Seidenberg on 26 Feb 2010 23:08 Excel 2007 Colors http://c0444202.cdn.cloudfiles.rackspacecloud.com/10_31_09a.xlsm
From: Holger Gerths on 1 Mar 2010 06:19 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" <herbds7-msxls(a)yahoo.com> schrieb im Newsbeitrag news:c5e957f4-5df0-4d79-a0f9-319cb0d2b552(a)t9g2000prh.googlegroups.com... > Excel 2007 Colors > http://c0444202.cdn.cloudfiles.rackspacecloud.com/10_31_09a.xlsm
From: Herbert Seidenberg on 1 Mar 2010 11:55 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
From: Holger Gerths on 5 Mar 2010 08:38
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. "Herbert Seidenberg" <herbds7-msxls(a)yahoo.com> schrieb im Newsbeitrag news:b0cd3515-de8e-471f-baff-c9bf29939263(a)s36g2000prh.googlegroups.com... > 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 > |