Line Weight of Marker Outline versus Series Line

4
votes

For a line chart, in the Excel UI:

  • You can manually change the line weight of the series line in 'Format Data Series', Line Style.

  • You can manually change the line weight of the marker outline in 'Format Data Series', Marker Line Style.

I haven't been able to distinguish these two in the VBA object model. For example, if you change each one in sequence while recording, the code is as follows:

With Selection.Format.Line ' This block is from Line Style
    .Visible = msoTrue
    .Weight = 1.5
End With
With Selection.Format.Line ' This block is from Marker Line Style
    .Visible = msoTrue
    .Weight = 2
End With

I want to write VBA code that will make the line between points thicker (like, 2 points), and the line around the point itself thinner (like 1 point). But if I use the code like above, changing one will change the other as well.

Thanks!

2
vbaexcel
Let me restate the desired end result: I want all line segments between points to be thicker, and all lines around the points to be thinner. (It can be achieved with the UI, but I haven't found a way to do it with code). Thanks! – Knom
Hi, I was wondering if you have found a solution yet. I am facing the exact same issue. – Charlie
Hi Charlie! Sorry, I haven't – Knom
I think @Excel Developers had the right idea, refer to the objects directly, instead of With Selection which is ambiguous context. Even so, it appears that .Format.Line for SeriesCollection(n) and .SeriesCollection(n).Point are linked: one clobbers the other (on a per-point basis), regardless of which order you put them in the code. – Knom

2 Answers

0
votes

Here you have a possible solution:

ActiveChart.SeriesCollection(1).Select

With Selection
   .Format.Line.Weight = 2 'Sets thickness = 2 to markers-line and series-line 
   .Border.Weight = xlHairLine 'Sets thickness = 0.25 to the series-line    
   .Border.Weight = xlThin 'Sets thickness = 1 to the series-line
   .Border.Weight = xlMedium 'Sets thickness = 2 to the series-line
   .Border.Weight = xlThick  'Sets thickness = 3 to the series-line
End With

This way you can set any weight to the markers-line, but you have only 4 possible weights for the series-line. And if you ever need to hide the series line (keeping the markers-line visible), just add the next code before "End with":

   .Border.LineStyle = xlNone
-2
votes
Dim co As ChartObject
Dim c As Chart
Dim s As Series
Dim p As Point

Set co = Sheet1.ChartObjects(1)
Set c = co.Chart
Set s = c.SeriesCollection(1)
Set p = s.Points(1)
p.Format.Line.Weight = 3