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.