Excel VBA PivotTable ShowDetails

Question

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.

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.

score 1 · Accepted Answer · answered Oct 22 '14 at 10:28

I worked it out!!! The following code will work exactly how 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 :)

score 0 · Answer 2 · answered Dec 08 '15 at 15:43

0

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.

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

answered Dec 08 '15 at 15:43

chukko

430
4
10

Sorry chukko, I've only just seen this. The ranges are coded, as they represent the column to be expanded/collapsed. I'm sure there are better ways to do what I'm doing, I'm still learning this craft :) – S8Tony Feb 01 '16 at 11:14
Sure. We are all learning something new every day. You need less code maintenance if you dont hardcode the exact columns, as each time you insert a new column to the left or place the table somewhere else you need to adjust the hardcoded values, which is a PITA. – chukko Feb 02 '16 at 14:06
chukko, this has been brilliant, I've now reduced my code by at least 7 lines of code.....doesn't sound much, but it all makes a difference, and it helps me to understand this thing better.......now I have a slightly different issue with setting the beveltoptype of my buttons based on the showdetail function, which used to work :( This used to work
```
s.ThreeD.BevelTopType = IIf(r.ShowDetail, 7, 3)
```
– S8Tony Feb 06 '16 at 10:39
good to hear that. For your q. - find out first if the left (s.ThreeD...) or the right side (r.ShowDetail) is the problem. If it is the former, please open a new question. If it is the latter, then r probably is not the single cell. – chukko Feb 08 '16 at 14:26
Chukko, you were absolutely right, it was the right side (r.Showdetail) which was causing me the problem. Now solved it using the following code Dim pi As PivotItem Set pi = pf.Pivotitems(1) s.ThreeD.BevelTopType = IIf(pi.Showdetail, 7, 3) – S8Tony Feb 09 '16 at 09:21

Excel VBA PivotTable ShowDetails

2 Answers2