3
I am trying to write an Excel 2010 VBA subroutine to format charts according to a predefined (i.e., defined by me) standard. The particular attributes that I want to be able to set are merely the attributes that become available through the Format Data Series window that opens when one double-clicks on a data series.
In an effort to discover the names of the various properties that I would need to set, I recorded as a macro the changes that I was making to the style of the data series. However, although I can set the Line Color, and the Marker Line Color to different colours via the Format Data Series window, the recorded macro (annotated below) refers to identically named (indistinguishable) objects for the Line Color and the Marker Line Color.
In addition, when I actually run the macro, there are two problems. First, despite the fact that the recorded macro refers to the marker-fill property .ForeColor.Brightness
, that line produces an error when the macros is executed. The error says “Method Brightness of object ColorFormat failed". Second the recorded code actually simultaneously sets the marker line colour and the main line for the series, so in the recorded code, they are both first set to what I hoped would be the Marker Line Color, and then both set to what I wanted for the main Line Color.
How do I set the Marker Fill, Marker Line Colour, and Line Colour.
Sub Macro1()
'
' Macro6 Macro
' On Sheet 1 there is a single embedded chrt
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText1
.ForeColor.TintAndShade = 0
' The following (recorded line produces an error)
.ForeColor.Brightness = 0.5
.Transparency = 0
.Solid
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent5
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0.400000006
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent6
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.5
.Transparency = 0
End With
End Sub
Your last two
with
are addressing the same object – Raystafarian – 2014-12-16T12:47:30.100@Raystafarian, I don't think you read the question. I know that they address the same object. That is the whole point of my question! Why did the macro recording of events that changed two different objects produce a macro that appears to reference the same object twice? And what objects should be referenced in the macro to effect the changes that I made manually? – user02814 – 2014-12-17T05:42:16.353
I have struggled with this. There are gaps in the way VBA handles the formatting of chart elements, when Office 2007 was rushed to market. The deficiencies have never been rectified, other than the broken
.TintAndShade
being left in for compatibility but replaced in 2010 by.Brightness
for functionality. – Jon Peltier – 2017-03-31T15:41:03.697