PDA

View Full Version : How do I set .MarkerBackgroundColor in a chart to a ThemeColor with TintAndShade?



Italophile
05-20-2014, 01:10 AM
I am attempting to modify some existing (working) code that I have for applying colors to charts to use theme colors. Most parts of the object model have the ThemeColor and TintAndShade properties but I have come unstuck with setting the MarkerBackgroundColor and MarkerForegroundColor as these simply take a long value.

Whilst Tony Jollans has written an excellent in-depth article on working with ThemeColors in Word the functions he has written for deriving the long value of a tinted themecolor only work for the wdThemeColor model, which is of course different to the msoThemeColor model that charts use.

Does anyone know how I can obtain the color value of a tinted themecolor so that I can pass it to the MarkerBackgroundColor?

Aflatoon
05-20-2014, 02:30 AM
You could assign the themecolor and tintandshade you want to a series (e.g. line) and then simply read the .Format.Line.ForeColor.RGB from that? Bit clumsy but fairly simple.

Italophile
05-20-2014, 12:35 PM
Thanks for your reply.

Unfortunately this workaround won't work for me. If an RGB colour is applied to an element it ceases to be theme aware.

My bad, I should have pointed out that I need all the chart elements to be theme aware so that as different themes are applied the chart updates automatically. In this specific situation my client has 12 different colour schemes and they don't use the same tints and shades that Excel applies. Once a chart has been created and formatted it may then be used in Word or PowerPoint and any of the 12 themes applied there.

Italophile
05-23-2014, 03:16 AM
:banghead:
My adventures in color continue...
I have found that the ColorFormat object changed in O2010 when a Brightness property was added. It seems this is used by Excel instead of TintAndShade. I had not noticed this before because I am, primarily, a Word developer. Whilst the Brightness property is there in the Word object model I have never had occasion to use it as the color of most objects can be set using a combination of ObjectThemeColor and TintAndShade.
Excel is different - TintAndShade works on some objects but not all. Where it doesn't work the Brightness property is used instead and takes the exact same value as TintandShade.
So to set my series markers to a theme color my code needs to select the series and run the following code twice:

With Selection.Format.Line
.Visible = msoTrue
With .ForeColor
.ObjectThemeColor = msoThemeColorAccent3
.Brightness = 0.25
End With
.Transparency = 0
End With


However, this still leaves the fill colour of the marker which stubbornly refuses to accept that it is theme aware once changed in code (i.e. change the theme from the UI and the marker fill colour doesn't update).