vba 循环通过报告过滤器来更改可见性不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14387938/
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
Looping through report filters to change visibility doesn't work
提问by user1507455
I'm trying to select one report filter, in this case Canada. That means the rest must be made invisible. This code works without issue:
我正在尝试选择一个报告过滤器，在本例中为加拿大。这意味着必须使其余部分不可见。这段代码可以正常工作：
Public Sub FilterPivotTable()
With ActiveSheet.PivotTables("Epidemiology").PivotFields("COUNTRY")
.PivotItems("Canada").Visible = True
.PivotItems("USA").Visible = False
.PivotItems("Germany").Visible = False
.PivotItems("France").Visible = False
End With
End Sub
However, I'm trying to prepare for when we add other countries to our "Epidemiology" pivot table, so I tried to have a for loop. This code doesn't work:
但是，当我们将其他国家/地区添加到我们的“流行病学”数据透视表时，我正在尝试做准备，因此我尝试使用 for 循环。此代码不起作用：
With ActiveSheet.PivotTables("Epidemiology").PivotFields("COUNTRY")
.PivotItems("Canada").Visible = True
For Each Pi In .PivotItems
If Pi.Value = "CANADA" Then
Pi.Visible = True
Else
Pi.Visible = False
End If
Next Pi
End With
It gives me an error on the Pi.Visible = Falseline. The error that I get is Run-time error '1004': Unable to set the Visible property of the PivotItem class
它给了我一个错误就Pi.Visible = False行了。我得到的错误是Run-time error '1004': Unable to set the Visible property of the PivotItem class
Why doesn't it work inside a for loop?!
为什么它不能在 for 循环中工作？！
Frustratingly, all the examples I find online use similar syntax. (Some use an index, but I tried that and got the same error.)
令人沮丧的是，我在网上找到的所有示例都使用类似的语法。（有些使用索引，但我尝试过并得到相同的错误。）
采纳答案by user1507455
In a pivottable filter, you must have at least one item selected at all times. Even if you intend to select one later in the code.
在数据透视表过滤器中，您必须始终至少选择一项。即使您打算稍后在代码中选择一个。
With Pt.PivotFields("COUNTRYSCENARIO")
' Sets all filters to true, resetting it.
.ClearAllFilters
' This is necessary if you want to select any options
' other than "All Pivot Items = Visible" and
' "OnlyOneSpecificPivotItem = Visible"
.EnableMultiplePageItems = True
If .PivotItems.Count > 0 Then
' goofy but necessary
Set firstPi = .PivotItems(1)
For Each Pi In .PivotItems
' Make sure that that first pivot item is visible.
' It gets mad if it's already visible and you
' set it to visible with firstPi.Visible = True
' ...pretty silly
If firstPi.Visible = False Then firstPi.Visible = True
' Don't loop through firstPi
If Pi.Value <> firstPi.Value Then
If Pi.Value = opt1 Or Pi.Value = opt2 Or Pi.Value = opt3 Then
Pi.Visible = True
ElseIf Pi.Visible = True Then
Pi.Visible = False
End If
End If
Next Pi
' Finally perform the check on the first pivot item
If firstPi = opt1 Or firstPi = opt2 Or firstPi = opt3 Then
firstPi.Visible = True
Else
firstPi.Visible = False
End If
End If
End With
Notice that if you try to select nothing, e.g. opt 1 = "" and opt2 = "" and opt3 = "", you will have that same error: you must have at least one pivot item selected.
请注意，如果您尝试不选择任何内容，例如 opt 1 = "" 和 opt2 = "" 和 opt3 = ""，您将遇到同样的错误：您必须至少选择一个数据透视项。
回答by Siddharth Rout
Is this what you are trying?
这是你正在尝试的吗？
Sub Sample()
Dim Pi As PivotItem
With ActiveSheet.PivotTables("Epidemiology").PivotFields("COUNTRY")
.PivotItems("Canada").Visible = True
For Each Pi In .PivotItems
If UCase(Pi.Value) = "CANADA" Then
Pi.Visible = True
Else
Pi.Visible = False
End If
Next Pi
End With
End Sub
