How to address Marker in scatter plot via VBA?

Question

I'm trying to set up a macro for a scatter plot where you switch the position of a data set in the legend and also format the markers.

This is my macro:

Sub FormatLegend

Dim ChtObj As ChartObject
Set ChtObj = Worksheets("Plot_1").ChartObjects("Diagramm 1")

With ChtObj

    With .Chart.SeriesCollection(1)
         .PlotOrder = 3
    End With

    With .Chart.SeriesCollection(4)
        .Format.Fill.Visible = msoTrue
        .Format.Line.Visible = msoFalse
        .Format.Fill.BackColor.RGB = RGB(146, 208, 80)
        .MarkerSize = 4
        .MarkerStyle = 2
        .Weight = 0.75
    End With
End Sub

I don't understand how to differentiate between line & marker. When I set .Format.Line.Visible = msoFalse the entire line disappears. I want the marker to remain visible.

How do I make the line invisible, but not the marker? Furthermore I want to set the width of the marker to 0.75 and to not apply a filling.

Hi @Alex L, sorry for my delayed reply. your way does work, thanks a lot! Great hint that .Border.LineStyle = xlLineStyleNone — Olli

Alex L Alex L · Accepted Answer · 2019-12-19T13:58:34

For me this works:

Sub Add_colour_scale_to_scatter()

    chart_name = "Chart 2"

    Set my_chart = ActiveSheet.ChartObjects(chart_name).Chart
    Set my_series = my_chart.FullSeriesCollection(1)

    my_colour = RGB(0, 176, 80) 'RGB(146, 208, 80)
    Debug.Print my_colour

    For i = 1 To my_series.Points.Count
        my_series.Points(i).Select
        With Selection 'my_series.Points(i)
            .MarkerForegroundColor = my_colour 'Border colour of the points
            .MarkerBackgroundColor = my_colour 'Colour of the points themselves

            .Format.Line.Weight = 0 'border of the point 0pt
            .Format.Line.Visible = msoFalse 'border of the point not visible
            .Format.Line.Transparency = 1 'border of the point is completely transparent

            .Border.Color = my_colour 'colour of the line between points
            .Border.LineStyle = xlLineStyleNone 'line between points is none. Others: 'xlContinuous 'continous line 'xlDot 'dotted line

            .Format.Fill.Visible = msoTrue 'the point is visible
            .Format.Fill.Solid 'the point has a solid fill
            .Format.Fill.ForeColor.RGB = my_colour
            .Format.Fill.Transparency = 0.3

            .MarkerStyle = 8 'round points
            .MarkerSize = 5 'size of the points
        End With
        If i Mod 100 = 0 Then
            DoEvents
            'Debug.Print i
            Application.StatusBar = i & " of " & my_series.Points.Count
        End If

    Next i

    MsgBox "done"

End Sub

The problem is that when you record a Macro it gives you the same code for the changing the line between points and for changing the line around points.

I believe that .Format.Line.Visible = msoFalse removes both the line between points and the line around points. Which is a little counter-intuitive. Doing the operations in this order and using .Border.LineStyle = xlLineStyleNone seems to be the best way to me. (i.e. use .Format.Line.Visible = msoFalse first to set the border of both the lines between and around points and then use .Border.LineStyle = xlLineStyleNone to set only the lines between points. Works for me)

I also have an example file here: https://drive.google.com/file/d/1HkeJVgKeFeCuj2ItRn2s90ozy41zlCVL/view?usp=sharing

For example if you change the line .Border.LineStyle = xlLineStyleNone,to the line .Border.LineStyle = xlContinuous,then this is the output:

And if you set it back to .Border.LineStyle = xlLineStyleNone, then you get no lines between:

(Note I used a dynamic function for colouring the points - you can see this here https://gist.github.com/Alex-ley/6fdaddda2b000072f70d98f90111a97e and in the linked file)

All xlLineStyles here: https://docs.microsoft.com/en-us/office/vba/api/excel.xllinestyle

How to address Marker in scatter plot via VBA?

1 Answers