Turn on / off the visibility of a series of diagrams using excel Macros / vba

Question

I am making a line graph (chart) in Excel, and several rows of data are built on the same chart.

I need to create a macro / VBA solution that can enable / disable the visibility of these series with the click of a button (or checkmark, etc.)

Like this image (manually executed via excel menu system)

I tried to view all member classes / methods on

https://msdn.microsoft.com/EN-US/library/office/ff837379.aspx

but they were out of luck.

I tried playing with bits like

Charts("Chart1").SeriesCollection(1)

and

Worksheets("Graphical Data").ChartObjects(1)

but I can’t get the chart object (I get an index out of range) and I can’t find any method that would allow me to enable / disable the visibility of individual series.

Any ideas?

Answer 1

, , , SeriesCollection.Format.Line.Visible. Excel ( 1-10) " 2" Sheet1.

:

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

, ser.Format.Line.Visible msoTrue .

, , cht ActiveChart. , ( , /).

Update

. SeriesCollection , , ( ), , .

.

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

, .Format.Line.Visible = msoTrue, ser.Name , .

Answer 2

, , , .

, Excel 2013, , - .

:

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

( ) , .

Answer 3

: . .   . , - . , . , , , , .