Turning the visibility of chart series on/off using excel Macros/vba

I believe the property you are looking for is the SeriesCollection.Format.Line.Visible property. I quickly created an Excel workbook and added a simple data set (just 1-10) and added a line graph "Chart 2" to the sheet Sheet1.

This code turned the visibility of the line off:

Option Explicit

Private Sub Test()
    Dim cht As Chart
    Dim ser As Series

    'Retrieve our chart and seriescollection objects'
    Set cht = Worksheets("Sheet1").ChartObjects("Chart 2").Chart
    Set ser = cht.SeriesCollection(1)

    'Set the first series line to be hidden'
    With ser.Format.Line
        .Visible = msoFalse
    End With

End Sub

And likewise, setting the ser.Format.Line.Visible property to msoTrue made the line visible again.

As for retrieving the chart itself I had to first activate it, then set my cht variable to the ActiveChart. To view the name of your chart, select it and look in the name box (near where you would enter the cell value / formula).

Update

When using the method above, the series name remains in the legend box. I couldn't find a visibility property for the SeriesCollection in the legend, however one workaround is to simply re-name the series as an empty string (this will make the series disappear from the legend) and then rename the series when you want to show it.

This code below will toggle the visibility of the line and series name in the legend.

Option Explicit

Private Sub Test()
    Dim cht As Chart
    Dim ser As Series

    'Retrieve our chart and seriescollection objects'
    Set cht = Worksheets("Sheet1").ChartObjects("Chart 1").Chart
    Set ser = cht.SeriesCollection(1)

    'Set the first series line to be hidden'

    With ser.Format.Line
        If .Visible = msoTrue Then
            .Visible = msoFalse
            ser.Name = vbNullString
        Else
            .Visible = msoTrue
            ser.Name = "Series 1"
        End If
    End With

End Sub

And, whenever you use .Format.Line.Visible = msoTrue just remember to set ser.Name back to whatever the name for your series is.


Whenever I don't know how to do something like this, I turn on the macro recorder.

I had a chart with four series, and I used the filter function in Excel 2013 to hide and show the second series, while the macro recorder was running.

Here's the relevant code:

ActiveChart.FullSeriesCollection(2).IsFiltered = True
' series 2 is now hidden
ActiveChart.FullSeriesCollection(2).IsFiltered = False
' series 2 is now visible

The series type (line or column) does not matter, this works for any of them.

Tags:

Excel

Vba

Charts