循环通过报告过滤器来改变可见性不起作用 [英] Looping through report filters to change visibility doesn't work

问题描述

我正在选择一个报告过滤器，在这种情况下为加拿大。这意味着其余的必须被隐形。
此代码无问题：

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

但是，当我们将其他国家添加到我们的流行病学数据透视表中时，我正在努力准备，所以我试图有一个for循环。此代码不起作用：

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:

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

它给了我一个错误，在$ code> Pi.Visible = False 行。我得到的错误是运行时错误'1004'：无法设置PivotItem类的可见属性

It gives me an error on the Pi.Visible = False line. The error that I get is Run-time error '1004': Unable to set the Visible property of the PivotItem class

为什么它不能在for循环中运行？

Why doesn't it work inside a for loop?!

令人沮丧的是，在线查找的所有示例都使用类似的语法。 （有些使用索引，但是我尝试了并且得到相同的错误。）

Frustratingly, all the examples I find online use similar syntax. (Some use an index, but I tried that and got the same error.)

推荐答案

在一个可枢纽的过滤器中，您必须具有在任何时候至少选择一个项目。即使您打算在代码中稍后选择一个。

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

请注意，如果您尝试不选择任何内容，例如opt 1 =，而opt2 =和opt3 =，您将会出现相同的错误：您必须至少选择一个枢纽项。

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.

这篇关于循环通过报告过滤器来改变可见性不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！