vba 使excel图表中的特定系列（线）不可见
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13885816/
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
Making a particular series (line) in an excel-chart invisible
提问by Jay Carr
So I have a graph that has 6 series on it and I want to be able to make each series visible or invisible using a set of ActiveX check boxes. The check boxes seem to be working fine so far, but I can't figure out how to make an individual series invisible. Is there a property of the series that I can use to make it not visible? Google and Microsoft help aren't turning it up for me.
所以我有一个图表，上面有 6 个系列，我希望能够使用一组 ActiveX 复选框使每个系列可见或不可见。到目前为止，复选框似乎工作正常，但我不知道如何使单个系列不可见。我可以使用该系列的属性使其不可见吗？谷歌和微软的帮助并没有为我打开。
(Granted, that doesn't mean it isn't there...)
（当然，这并不意味着它不存在......）
Any and all help is appreciated.
任何和所有的帮助表示赞赏。
回答by Scott Holtzman
Update
更新
Wait, I remember that I have come across this before in the past. A good way to hide the data you want is to hide the actual rows / columns of data being charted. So your ActiveX control won't touch the chart, but rather hide / unhide the columns / rows of chart data.
等等，我记得我以前遇到过这个。隐藏所需数据的一个好方法是隐藏正在绘制的实际数据行/列。因此，您的 ActiveX 控件不会触及图表，而是隐藏/取消隐藏图表数据的列/行。
You will just need to set up your chart data in such a way that it doesn't interfere with any other data that needs to be shown.
您只需要设置图表数据，使其不会干扰需要显示的任何其他数据。
Leaving my original answer for anyone's benefit...
为任何人的利益留下我的原始答案......
I just recorded a macro and played with it ...
我刚刚录制了一个宏并使用它...
Got this great result:
得到了这个伟大的结果：
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
'Turn it on
Selection.Format.Line.Visible = msoTrue
'Turn it off
Selection.Format.Line.Visible = msoFalse
This is eqiuvalent to selecting No Linein the Line Color section of the Format Data Series dialog box.
这等效于No Line在“设置数据系列格式”对话框的“线条颜色”部分中进行选择。
回答by Aaron Clark
When I get stumped in Excel, I always try recording a macro, a la WWBGD? (What Would Bill Gates Do?)
当我在 Excel 中被难住时，我总是尝试录制一个宏，就像 WWBGD？（比尔盖茨会做什么？）
Here's the result for a bar chart where I set the fill to "None":
这是我将填充设置为“无”的条形图的结果：
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
Selection.Format.Fill.Visible = msoFalse
Does that help?
这有帮助吗？
回答by Anirudh Ramanathan
The above 2 did not workfor me, in case of ChartType = xlLineMarkers.
What did work was:
以上 2对我不起作用，以防万一ChartType = xlLineMarkers。有效的是：
For Each mySeries In .SeriesCollection
With mySeries
.Border.LineStyle = xlNone
.MarkerStyle = xlNone
.MarkerBackgroundColorIndex = xlColorIndexNone
End With
Next
However, the legend does still display the line. It is necessary to delete that entry separately.
但是，图例仍然显示该行。有必要单独删除该条目。
回答by user4825591
In Excel 2013, to make a serie invisible, taking it also out of the legend etc, use the filter option, . Vba Example:
在 Excel 2013 中，要使序列不可见，也将其从图例中取出等，请使用过滤器选项 . VBA 示例：
The Following code Hides / Restores the series by filtering it Out/In of the chart. ActiveSheet.ChartObjects("Chart 1").Chart.FullSeriesCollection(1).IsFiltered = True / False
以下代码通过从图表中过滤出/入来隐藏/恢复系列。ActiveSheet.ChartObjects("图表 1").Chart.FullSeriesCollection(1).IsFiltered = True / False
This is equivalent to manually filtering a series in/out using the Ribbon command Design + Select Data, (which is available when the char is selected).
这等效于使用功能区命令设计 + 选择数据（在选择字符时可用）手动过滤输入/输出系列。
All series options, like dashes colors, markers etc.. are restored unchanged.
所有系列选项，如破折号颜色、标记等，都将恢复不变。
回答by Wheelie22
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(linenumber).Select
Selection.Format.Fill.Visible = msoFalse
This works great, select the chart than i have 24 lines, with a listview with checkboxes and with 24 entries. Click on 1 and it disapears, click again and its back. You alsow see the line dis- and appear in the legend.
这很好用，选择图表而不是我有 24 行，带有复选框和 24 个条目的列表视图。单击 1 并消失，再次单击并返回。您还会看到线 dis- 和出现在图例中。
