Macro to toggle Pivot - Collapse/Expand entire field

[rasonline]

Hey guys

Need your help with VBA and pivottables.

I want to select a field in a pivottable, and then be able to click a macro button to toggle the "collapse/expand" entire field. I need a way to reference the pivot field represented by the cell that has been selected.

(Post edited and removed unnecessary info).

[AlphaFrog]

Try something like this (not tested).

Sub PvotField_Toggle_Detail()
    Dim pt As PivotTable, pf As PivotField
    
    For Each pt In ActiveSheet.PivotTables                                    'loop through all pivot tables
        If Not Intersect(pt.TableRange1, ActiveCell) Is Nothing Then          'test if the active cell is within a pivot table
            For Each pf In pt.PivotFields                                     'loop through pivot fields
                If Not Intersect(pf.LabelRange, ActiveCell) Is Nothing Then   'test if the active cell intersects pivot field
                    'Debug.Print pf.Name
                    pf.ShowDetail = Not pf.ShowDetail                         'Toggle field state
                    Exit Sub
                End If
            Next pf
        End If
    Next pt
    
End Sub

You may find this link helpful:
Referencing an Excel Pivot Table Range using VBA

[rasonline]

note that this line of code was also generating an error, so I quoted it out

If Not Intersect(pf.LabelRange, ActiveCell) Is Nothing Then   'test if the active cell intersects pivot field

[AlphaFrog]

What exactly are you selecting before you run the macro? Your description of events is confusing and sometimes contradictory.

• I want to select a field in a pivottable,...
• How do I replace the PivotFields("Project Name") with something that will be based on the active cell that the user selects in the pivottable?
• So, in practice, I will select a cell in the pivottable, ...
• I only need help with the code to select the pivotfield.
• When I place the cell outside the pivottable, then the macro executes but with no result or change on the page

[rasonline]

thanks for the reply. Unforutnately did not work.

When I place the cell outside the pivottable, then the macro executes but with no result or change on the page
I know this because I added "msgbox Done" at the end, and it does this.

when I select a cell inside the pivottable, then I get a DEBUG error on this line

    Dim pt As PivotTable, pf As PivotField
    Set pt = ActiveSheet.PivotTables(1)
    
    With pt
        If Not Intersect(pt.TableRange1, ActiveCell) Is Nothing Then          'test if the active cell is within a pivot table
            For Each pf In pt.PivotFields                                     'loop through pivot fields
                'If Not Intersect(pf.LabelRange, ActiveCell) Is Nothing Then   'test if the active cell intersects pivot field
                    'Debug.Print pf.Name
                    pf.ShowDetail = Not pf.ShowDetail                         'Toggle field state
                    Exit Sub
                'End If
            Next pf
        End If
    End With
    
    MsgBox "Done"

[rasonline]

Sorry for the confusion.

What I need to achieve - I click a cell inside a pivot table. Then click a macro button and it should collapse/expand the entire field. That's it.

I should be able to use this macro on any pivot table, in any workbook.


(Ignore references in previous post about clicking outside pivot table. That was only for purposes of diagnosing why your code did not work)

[rasonline]

The pivot table display is Classic view, so each field is in a separate column.

[rasonline]

excelquery.png

here is a screenshot to demonstrate the request.
Want to place the cursor in the first column, then click a button and collapse all groups.

[rasonline]

Ok - i've managed to solve it. Here is the solution for anybody else who wants it.

   Dim PfName As String
    Dim Pf As PivotField
    
    PfName = ActiveCell.PivotField.Name
    
    ActiveSheet.PivotTables(1).PivotFields(PfName).ShowDetail = False

[gothic chicken]

Ok - i've managed to solve it. Here is the solution for anybody else who wants it.

   Dim PfName As String
    Dim Pf As PivotField
    
    PfName = ActiveCell.PivotField.Name
    
    ActiveSheet.PivotTables(1).PivotFields(PfName).ShowDetail = False

Although the "PfName" code is helpful, and is at least as concise as any other I've found thanks, that code doesn't actually toggle the "collapse/expand" function; it simply collapses the selected field.
All the research I've done hasn't come up with a one-button toggle function, so it seems the next best option is to have a button where "ShowDetail = False" and another where "ShowDetail = True".

[rasonline]

Help... ok. Here is the most simple way of describing it. I need a macro to do the same as the excel command -
expand.collapse.jpg

I want to assign it to button on my Quick Access Toolbar.

[YasserKhalil]

Cross-Post at this link
http://chandoo.org/forum/threads/exp...eld-vba.14326/

[AliGW]

Rasonline - please read the forum rules on cross-posting and make sure that you comply with them in future: https://www.excelforum.com/forum-rul...rum-rules.html

[rasonline]

Thanks, noted and will apply for future reference.

[AlphaFrog]

I think you could shorten it to this for what it's worth.

ActiveCell.PivotField.ShowDetail = False

[rasonline]

Resurrecting this thread - trying this again in 2024.

Why would the messagebox in this code give a FALSE???
The code successfully expands the pivottable field! So the field properly is definitely TRUE.

But trying to capture that property as a variable still yields FALSE....??
I even tried using "As Long" but unable to extract a TRUE or 1.

Sub Pivot_ExpandFields()
    
    Dim PfName As String
    Dim Pf As PivotField
    Dim PfStatus As Boolean
    
    PfName = ActiveCell.PivotField.Name
    ActiveSheet.PivotTables(1).PivotFields(PfName).DrilledDown = True
    
    MsgBox ActiveSheet.PivotTables(1).PivotFields(PfName).DrilledDown  'msgbox to display the current drilleddown property      
      this msgbox generates "FALSE" when it should be "TRUE"

End Sub

_________________________________________________________________________________________________________
Ideally, I want to create a toggle button like this (as opposed to the standard separate expand and collapse buttons):

Sub PivotField_Toggle()
    
    Dim PfName As String
    Dim Pf As PivotField
    
    PfName = ActiveCell.PivotField.Name
    
    If Not ActiveSheet.PivotTables(1).PivotFields(PfName).DrilledDown = False Then
        ActiveSheet.PivotTables(1).PivotFields(PfName).DrilledDown = False
        Else: ActiveSheet.PivotTables(1).PivotFields(PfName).DrilledDown = True
    End If
                
End Sub

Any suggestions?

[AliGW]

The thread is marked as solved - remove that if you want further assistance.

[rasonline]

The thread is marked as solved - remove that if you want further assistance.

thanks for that. Now updated thread title to remove "solved"