Excel VBA 更改数据透视图中系列的颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20426837/
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
Excel VBA Change Color of Series in Pivot Chart
提问by Trevor Eyre
This is one of those stupid questions that should be really simple, but I can't quite figure out what's wrong, and everything I've found on the Internet hasn't worked.
这是那些应该非常简单的愚蠢问题之一，但我无法弄清楚出了什么问题，而且我在互联网上找到的所有内容都没有奏效。
I just want to recolor the series line in a pivot chart after I change the filters in the pivot table. Here is what I have tried:
我只想在更改数据透视表中的过滤器后重新着色数据透视图中的系列线。这是我尝试过的：
ThisWorkbook.Charts(ChartName).SeriesCollection(SeriesName) _
Format.Line.ForeColor = RGB(75, 172, 198)
That code gives me "subscript out of range" error. I also tried:
该代码给了我“下标超出范围”错误。我也试过：
Sheets(SheetName).ChartObjects(ChartName).Chart.SeriesCollection(SeriesName) _
.Format.Line.ForeColor = RGB(75, 172, 198)
as well as
也
... .ForeColor.ColorIndex = 3
as well as
也
... .ForeColor.ObjectThemeColor = msoThemeColorAccent4
But those all give me a "type mismatch" error. Excel's VBA Recorder gives me:
但这些都给了我一个“类型不匹配”的错误。Excel 的 VBA 记录器给了我：
ActiveChart.SeriesCollection(2).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent4
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0.400000006
.Transparency = 0
End With
I'm sure I just have some stupid, obvious error somewhere, but I can't for the life of me figure out how to access the color property of a series in a pivot chart. Sometimes it feels like trying to navigate a freakin' labyrinth trying to figure out the cryptic hierarchy of objects that a certain property belongs to in VBA...
我确定我只是在某处遇到了一些愚蠢的、明显的错误，但我终其一生都无法弄清楚如何访问数据透视图中系列的颜色属性。有时感觉就像试图在一个怪异的迷宫中导航，试图找出 VBA 中某个属性所属的对象的神秘层次结构......
采纳答案by Sam
It seems that you have the makings of a solution with your attempts. A few things that I found (lots of it in XL Help):
似乎您已经通过尝试找到了解决方案。我发现的一些东西（XL 帮助中有很多）：
- Format is a read-only property, which might explain why that wasn't working.
- Use ChartGroups instead of SeriesCollection
- From XL Help "There's no object that represents a single series line; you either have series lines turned on for all points in a chart group or you have them turned off."
- Instead of declaring the seriescollection, try using Charts(ChartName).ChartGroup(cg)
- Format 是一个只读属性，这可以解释为什么它不起作用。
- 使用 ChartGroups 而不是 SeriesCollection
- 来自 XL 帮助“没有代表单个系列线的对象；您要么为图表组中的所有点打开系列线，要么关闭它们。”
- 不要声明系列集合，而是尝试使用 Charts(ChartName).ChartGroup(cg)
So, your code might look like:
因此，您的代码可能如下所示：
Dim cht as Chart
Dim cg as ChartGroup
Set cht = ActiveSheet.ChartObjects(ChartName)
With cht.ChartGroups(cg)
.HasSeriesLines = True 'turn on series lines for the chart group
With .SeriesLines.Border
.ColorIndex=[pick your color]
End With
End With
回答by David Zemens
What I discover from debugging a simple example is that apparently you cannot assign a .Nameproperty to series in a PivotChart. I would expect this to raise an error, but it does not. It just passes right over the assignment as if nothing happened.
我从调试一个简单的例子中发现，显然你不能.Name为数据透视图中的系列分配一个属性。我希望这会引发错误，但事实并非如此。它就好像什么都没发生一样直接通过了任务。
Sub Test()
Dim pivotChart As ChartObject
Dim cht As Chart
Dim srs As Series
Dim seriesName As String
Set pivotChart = ActiveSheet.ChartObjects(1)
Set cht = pivotChart.Chart
Set srs = cht.SeriesCollection(1)
'First look at the name:
Debug.Print srs.Name
'Assign a new name to the string variable
seriesName = "Name"
'Assign the new name from string var to the series
srs.Name = seriesName
'Now review that the name has changed. It hasn't!!
Debug.Print ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Name
End Sub
