使用 excel Macros/vba 打开/关闭图表系列的可见性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29703066/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Turning the visibility of chart series on/off using excel Macros/vba
提问by unknownSPY
I am making a line graph (chart) in Excel with several data series being plotted onto the same chart.
我正在 Excel 中制作折线图（图表），并将多个数据系列绘制到同一个图表上。
I need to create a macro/VBA solution that can turn the visibilty of these series on/off via the pressing of a button (or tick box etc)
我需要创建一个宏/VBA 解决方案，可以通过按下按钮（或复选框等）打开/关闭这些系列的可见性
Similar to this picture (manually done through the excel menu system)
类似于这张图（通过excel菜单系统手动完成）
I have tried to look through all the member vars/methods on
我试图查看所有成员变量/方法
https://msdn.microsoft.com/EN-US/library/office/ff837379.aspx
https://msdn.microsoft.com/EN-US/library/office/ff837379.aspx
but haven't had much luck.
但没有多少运气。
I have tried playing around with bits like
我试过玩一些像
Charts("Chart1").SeriesCollection(1)
and
和
Worksheets("Graphical Data").ChartObjects(1)
but I can neither get the chart object ( I get a subscript out of range error) nor able to find any method that would allow me to turn on/off the visibility of individual series.
但我既无法获得图表对象（我得到一个下标超出范围的错误），也无法找到任何允许我打开/关闭单个系列可见性的方法。
Any Ideas?
有任何想法吗？
回答by Jon Peltier
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.
我有一个包含四个系列的图表，我使用 Excel 2013 中的过滤器功能来隐藏和显示第二个系列，同时宏记录器正在运行。
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.
系列类型（行或列）无关紧要，这适用于其中任何一个。
回答by Soulfire
I believe the property you are looking for is the SeriesCollection.Format.Line.Visibleproperty. 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.
我相信你正在寻找的SeriesCollection.Format.Line.Visible财产就是财产。我快速创建了一个 Excel 工作簿并添加了一个简单的数据集（仅 1-10 个），并在工作表 Sheet1 中添加了一个折线图“图表 2”。
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.Visibleproperty to msoTruemade the line visible again.
同样，将ser.Format.Line.Visible属性设置为msoTrue使线条再次可见。
As for retrieving the chart itself I had to first activate it, then set my chtvariable 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).
至于检索图表本身，我必须先激活它，然后将我的cht变量设置为 ActiveChart。要查看图表的名称，请选择它并查看名称框（靠近您将输入单元格值/公式的位置）。
Update
更新
When using the method above, the series name remains in the legend box. I couldn't find a visibility property for the SeriesCollectionin 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.
使用上述方法时，系列名称保留在图例框中。我SeriesCollection在图例中找不到 的可见性属性，但是一种解决方法是简单地将该系列重命名为空字符串（这将使该系列从图例中消失），然后在您想要显示时重命名该系列它。
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 = msoTruejust remember to set ser.Nameback to whatever the name for your series is.
而且，每当您使用时.Format.Line.Visible = msoTrue，请记住设置ser.Name回您系列的任何名称。
回答by Seyed Ababs Ziaei
There is a simple way to on & off the visibility of the series: using filter on your source data. May it help you easily as follows. You can insert a new Window. Setone of them to source data sheet and the other window to Chart sheet. Then arrange the two windows to see both at the same time. Now if you filter the series you like on the source data sheet simultaneously you will see the series you desired on the other sheet.
有一种简单的方法可以打开和关闭系列的可见性：对源数据使用过滤器。愿它可以帮助您轻松如下。您可以插入一个新窗口。将其中一个设置为源数据表，另一个窗口设置为图表表。然后将两个窗口排列起来，以便同时查看。现在，如果您同时在源数据表上过滤您喜欢的系列，您将在另一张表上看到您想要的系列。
