我认为这是这里的主要问题,我建议一个没有参数和错误处理的例程,像这样:Sub Hide_PivotItem(PivotTable_Object As PivotTable, _ PivotField_Name As String, _ ByVal PivotItem_Name As String, _ ByVal UnHide_All As Boolean) Dim Pt As PivotTable, _ Pi As PivotItem, _ Pf As PivotField Set Pt = PivotTable_Object Set Pf = Pt.PivotFields(PivotField_Name) Pt.PivotCache.MissingItemsLimit = xlMissingItemsNone Pt.PivotCache.Refresh If UnHide_All Then On Error Resume Next For Each Pi In Pf.PivotItems Pi.Visible = True Next Pi On Error GoTo 0 Else 'Don't unhide other items End If For Each Pi In Pf.PivotItems If Pi.Name <> PivotItem_Name Then Else Pi.Visible = False End If Next PiEnd SubI have two sets of code. For some reason, in the first one I get an error, and in the second one I don't.1:Dim pi As PivotItemDim pf As PivotFieldSet pf = Sheets("Afname per school").PivotTables("Draaitabel3").PivotFields("school")For Each pi In pf.PivotItems If pi = "(leeg)" Then pi.Visible = False Else pi.Visible = True 'ERROR HERE End IfNext pi2:Dim pi As PivotItemDim pf As PivotFieldSet pf = Sheets("Afname per school").PivotTables("Draaitabel3").PivotFields("naam locatie")For Each pi In pf.PivotItems If InStr(pi, "BSO") Then pi.Visible = True Else pi.Visible = False End IfNext piI get the error: "unable to get the visible property of the pivotitem class"I read I should solve this the following:I tried to add this line of code in two ways:Dim pi As PivotItemDim pf As PivotFieldSet pf = Sheets("Afname per school").PivotTables("Draaitabel3").PivotFields("school")pt.PivotCache.MissingItemsLimit = xlMissingItemsNone '1st TRYFor Each pi In pf.PivotItems pt.PivotCache.MissingItemsLimit = xlMissingItemsNone '2nd TRY If pi = "(leeg)" Then pi.Visible = False Else pi.Visible = True End IfNext piThis doesn't seem to solve my problem. 解决方案 1. Do you have multiples fields in your rows/columns fields?Because the problem might be coming from this.All the PivotItems in PivotField are not always displayed/displayable because they are in the second level, depending on the first level. To avoid code breaking because of the error, you'll have to use an Error Handler.Only the PivotItems that are found with the corresponding PivotItem from the first level are displayable (IE you can't displayed a case that didn't happen in your data).2. Refresh PivotCacheThat being said, you could simply refresh the pivot cache (check that you have defined Pt as your PivotTable) right after using the setting MissingItemsLimit to be sure that you have the most recent data :Set Pt = Sheets("Afname per school").PivotTables("Draaitabel3")Set pf = Pt.PivotFields("school")Pt.PivotCache.MissingItemsLimit = xlMissingItemsNonePt.PivotCache.Refresh3. Code logicLooking at your code afterwards, I'm a bit perplex, because what you are doing is hiding a specific PivotItem found by his name, but you also try to display every other PivotItem!And I think that is the main problem here, I would suggest a routine with few arguments and error handling, something like this :Sub Hide_PivotItem(PivotTable_Object As PivotTable, _ PivotField_Name As String, _ ByVal PivotItem_Name As String, _ ByVal UnHide_All As Boolean) Dim Pt As PivotTable, _ Pi As PivotItem, _ Pf As PivotField Set Pt = PivotTable_Object Set Pf = Pt.PivotFields(PivotField_Name) Pt.PivotCache.MissingItemsLimit = xlMissingItemsNone Pt.PivotCache.Refresh If UnHide_All Then On Error Resume Next For Each Pi In Pf.PivotItems Pi.Visible = True Next Pi On Error GoTo 0 Else 'Don't unhide other items End If For Each Pi In Pf.PivotItems If Pi.Name <> PivotItem_Name Then Else Pi.Visible = False End If Next PiEnd Sub 这篇关于无法获取pivotitem类的可见属性的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!