Excel VBA 数据透视表显示详细信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26504218/
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 PivotTable ShowDetails
提问by Tony Davis-Coyle
I have a pivot table that contains a number of Expand/Collapse buttons related to Customer, Year, and Quarters all of which have vba behind them to .Showdetails as appropriate. These individual sets of code work. However, I'm trying to make my code more efficient, and manageable, and thanks to another user, I'm getting closer.
我有一个数据透视表，其中包含许多与 Customer、Year 和 Quarters 相关的展开/折叠按钮，所有这些按钮后面都有 vba 到 .Showdetails 视情况而定。这些单独的代码集工作。但是，我正在努力使我的代码更高效、更易于管理，并且感谢另一个用户，我越来越接近了。
Here's the code for each button that works:
这是每个有效按钮的代码：
Sub Expand_Quarter()
Range("R13").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Quarter").ShowDetail = IIf(Selection.ShowDetail, False, True)
End Sub
And Here is the code that is throwing an error. I've commented where the error occurs:
这是引发错误的代码。我已经评论了错误发生的地方：
Sub ExpColl()
Dim pt As PivotTable, pf As PivotField, b As String
b = Application.Caller
With ActiveSheet
Set pt = .PivotTables("PivotTable1")
Select Case b
Case "btnExpCollCustProd": Set pf = pt.PivotFields(.Range("Q15").PivotField.Name)
Case "btnExpCollYear": Set pf = pt.PivotFields(.Range("R12").PivotField.Name)
Case "btnExpCollQtr": Set pf = pt.PivotFields(.Range("R13").PivotField.Name)
End Select
'--- Run Time Error 1004 Application-defined or Object Defined Error"
pf.ShowDetail = IIf(pf.ShowDetail, False, True)
End With
End Sub
The variable pf does return exactly what I expect, so I'm a little perplexed. All help greatly appreciated.
变量 pf 确实返回了我所期望的，所以我有点困惑。非常感谢所有帮助。
采纳答案by Tony Davis-Coyle
I worked it out!!! The following code will work exactlyhow I want it to:
我解决了！！！以下代码将完全按照我的意愿工作：
Sub ExpColl()
Dim pt As PivotTable, pf As PivotField, b As String, s As Shape, bev As Long, r As Range
b = Application.Caller
Set s = ActiveSheet.Shapes(b)
If b Like "btnExpColl*" Then
With ActiveSheet
Set pt = .PivotTables("PivotTable1")
Select Case b
Case "btnExpCollCustProd":
Set r = .Range("Q15")
Set pf = pt.PivotFields(r.PivotField.Name)
Case "btnExpCollYear":
Set r = .Range("R12")
Set pf = pt.PivotFields(r.PivotField.Name)
Case "btnExpCollQtr":
Set r = .Range("R13")
Set pf = pt.PivotFields(r.PivotField.Name)
End Select
pf.ShowDetail = IIf(r.ShowDetail, False, True)
s.ThreeD.BevelTopType = IIf(s.ThreeD.BevelTopType = 3, 7, 3)
End With
End If
End Sub
I hope this helps someone else out there :)
我希望这可以帮助其他人:)
回答by chukko
I assume this problem might be related to the fact that each value of the field might have different value of ShowDetail property, so Excel does not even try to return it even if all the values were identical.
我认为这个问题可能与字段的每个值可能具有不同的 ShowDetail 属性值有关，因此即使所有值都相同，Excel 甚至不会尝试返回它。
One way to avoid hardcoding specific ranges (which depends on the physical layout of the pivot table) is to use the following code:
避免硬编码特定范围（取决于数据透视表的物理布局）的一种方法是使用以下代码：
pf.ShowDetail = Not pf.DataRange.Cells(1).ShowDetail
