How do I set the "marker line style" with VBA

Hi,

In Excel 2007 I can right-click on a chart series (XYScatter) and click "Marker Line Style" on the left. I can then set the marker's line Width, Compound Type, Dash type, Cap type, Join type, Arrow settings(!) and finally a Smooth line checkbox.

Obviously none of that gets recorded by the macro recorder....

Please can you tell me, how do I do all of that with VBA?

Is there anything else that's new from 2003 to 2007 that I should know about re. formatting data series? All tips gratefully received.

Cheers
Rich

[UPDATE:

So I've done some more digging online and stumbled across a post by the great Andy Pope on another site.

With a bit of experiementing with what he put here, I've figured out that using:

ActiveChart.SeriesCollection(1).Format.Line.Weight = 12

sets the weight (thickness) of the marker lines AND the series line (i.e. lines between each data point).

It appears you then have to reapply the desired formatting to the series line using:

ActiveChart.SeriesCollection(1).Border.Weight = 2

Someone a Microsoft must be very proud of that day's work, forced to come in the day after the office Christmas party perhaps?

So, to set the mark line thickness, we use:

    Dim ser As Series
    Set ser = ActiveChart.SeriesCollection(1)
    ser.Format.Line.Weight = 12   'Sets both marker line and series line thickness
    ser.Border.Weight = 2               'Reapply series line thickness only

I also discovered that the Arrow settings are only applicable to the following marker styles: + x and *, or xlMarkerStylePlus, xlMarkerStyleX and xlMarkerStyleStar.

Still looking for how to set the other properties (Compound Type, Dash type, Cap type, Join type, Arrow settings and the Smooth line checkbox). Any takers?]

This thread is locked. You can vote as helpful, but you cannot reply or subscribe to this thread.

Answer

Andreas Killer

Not all settings have a visible effect on the chart. And some settings to override other, as you've probably noticed.

The settings for the markers can be found in the points of the series, but this settings overwrides also some settings of the series line.

Andreas.

Sub Test()
Dim S As Series
Dim P As Point
Set S = ActiveChart.SeriesCollection(1)
For Each P In S.Points
    With P
      .MarkerStyle = xlMarkerStyleCircle
      .Format.Line.DashStyle = msoLineSysDot
      .Format.Line.Weight = 20
    End With
Next
S.Border.Weight = 2
End Sub

Contribute to the Microsoft 365 and Office forum!

Excel Forum Top Contributors:

How do I set the "marker line style" with VBA

Replies (6)

Question Info

How do I set the "marker line style" with VBA

Replies (6) 

Question Info

Replies (6)