vba 无法设置 PivotItem 类的 Visible 属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10471148/
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
Unable to set the Visible property of the PivotItem class
提问by Ramify
Run-time error '1004': Unable to set the Visible property of the PivotItem class
运行时错误“1004”：无法设置 PivotItem 类的 Visible 属性
Excel VBA:
Excel VBA：
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pf = pt.PivotFields("Snapshot Date")
For Each pi In pf.PivotItems
If pi.Name <> "(blank)" Then
pi = DateValue(pi) 'I have tried using Cdate, Cdbl, Cstr as well.
End If
Next pi
Errors happen here:
错误发生在这里：
i = 1
Do Until i >= pf.PivotItems.count - 1
For Each pi In pf.PivotItems
pi.Visible = False 'Error here
Next pi
pf.PivotItems(i).Visible = True '.. And here!
pf.PivotItems(i + 1).Visible = True
Loop
The pivot items are dates, not sure if I need to format them before I can turn visibility on/off?
数据透视项是日期，不确定在打开/关闭可见性之前是否需要格式化它们？
I have googled endlessly and could not find any solution to this :( Something about non-contiguous items, but I don't quite understand.
我无休止地用谷歌搜索，找不到任何解决方案:(关于非连续项目的东西，但我不太明白。
回答by Dick Kusleika
You have to have at least one visible PivotItem and you're setting them all to invisible. You need to loop through all the PivotItems, set the first one to visible = True, then test all the rest and set them accordingly, then test the first one and set it accordingly. That way you'll be sure there's always one visible PivotItem.
您必须至少有一个可见的 PivotItem 并且您将它们全部设置为不可见。您需要遍历所有 PivotItems，将第一个设置为visible = True，然后测试所有其余的并相应地设置它们，然后测试第一个并相应地设置它。这样您就可以确保始终有一个可见的 PivotItem。
However, I'm not really sure what your test is. You have a loop, but don't increment "i", so it will loop forever always trying to set PivotItems 1 and 2 to visible.
但是，我不确定您的测试是什么。您有一个循环，但不增加“i”，因此它将永远循环，始终尝试将 PivotItems 1 和 2 设置为可见。
I don't know why you're getting that second error, but if you can explain what you're trying to do there may be a better way.
我不知道你为什么会收到第二个错误，但如果你能解释你想要做什么，可能会有更好的方法。
回答by Dick Kusleika
For anyone that finds this page using google (like I did), i found a way around this.
对于使用 google 找到此页面的任何人（就像我所做的那样），我找到了解决此问题的方法。
The problem appears to be only setting visible = true.
问题似乎只是设置visible = true。
Setting visible = falseseems to work fine.
设置可见 = false似乎工作正常。
If you disable the multiple selections and then re-enable it, all categories are then selected.
如果您禁用多项选择，然后重新启用它，则会选择所有类别。
From that point you can then loop through and set visible = false to the ones you want.
从那时起，您可以循环遍历并将可见 = false 设置为您想要的。
See my example (working) code below :-
请参阅下面的示例（工作）代码：-
Dim table As PivotTable
Dim PvI As PivotItem
Set table = ActiveSheet.PivotTables("PivotTable3")
With table.PivotFields("ERROR CODE")
.ClearAllFilters
.EnableMultiplePageItems = False
.CurrentPage = "(All)"
.EnableMultiplePageItems = True
For Each PvI In .PivotItems
Select Case PvI.Name
Case "Err0"
PvI.Visible = False
End Select
Next
End With
Hope this helps someone.
希望这可以帮助某人。
