• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Expand (or collapse) entire field VBA

A

Alexis

Member
Hi,

I have a pivot table report which is to be sent to a group of users who are unfamiliar with pivot tables.

Above the pivot table I'd like to place 2 buttons; Expand entire field, and collapse entire field.

If I try and explain where these are in the pivot table tools tab they will get lost, even with screen shots, so thought it would be easier just to have two buttons above the table.

When I use the macro recorder it records the name of the field that the active cell is on, but I'd like the code to say "expand whichever field the activecell is on" so they can just keep clicking.

Any help is much appreciated.

Regards,
Alexis
 
try..
* Adding Two Button..
for 1st Button.. use code as
Code: 
ActiveSheet.PivotTables(1).PivotFields(1).ShowDetail = True
and for 2nd one FALSE

Give it a try.. I guess.. you already have prior knowledge of Pivot Coding.. you can manage .. Indexing of Pivot Table Names or Fields. :)
 
Debraj said:
try..
* Adding Two Button..
for 1st Button.. use code as
Code: 
ActiveSheet.PivotTables(1).PivotFields(1).ShowDetail = True
and for 2nd one FALSE

Give it a try.. I guess.. you already have prior knowledge of Pivot Coding.. you can manage .. Indexing of Pivot Table Names or Fields. :)
Click to expand...

Resurrecting this thread. Can you help me with a similar query but I don't know how to index the pivotfields?

I want to create a button with a macro to do the following: I select a field in a pivottable, then press the button to toggle the expand/collapse state of that field. The pivottable is in Classic view so fields are in separate columns.

This macro must work on any pivvottable, in any workbook.

How do i reference the pivotfield of the activecell??

thanks.
 
NARAYANK991 said:
Hi ,

ActiveCell.Pivotfield references the pivotfield where the cursor is placed.

Narayan
Click to expand...

Thanks, but still struggling with how to use this.
I've tried the following code but it gives me an error.

Code: 
Sub collapsegroups()
   
    Dim Pf As PivotField
    Pf = ActiveCell.PivotField
   
    ActiveSheet.PivotTables(1).Pf.ShowDetail = False
   
    MsgBox "done"
   
   
End Sub
 
here is a workbook with dummy data, showing the pivottable and the desired result.

upload_2017-8-5_12-29-25.png
 

Attachments

  • ExcelForum1.xlsm
    24.3 KB · Views: 31
NARAYANK991 said:
Hi ,

See the file now.

Narayan
Click to expand...

This will not work. In the code you provided, "Column 1" is hardcoded as the Pivotfield name. I want a method to input a generic field name. For example, today I'm working on this particular pivottable, and I want to collapse column1.

Tomorrow, i'm working on another pivottable, in another file and I want to collapse a field called "Sales location".

The next day I'm working on another pivottable with a field called "Fiscal Month" or any other field.

How do I get the macro to expand/collapse?

This is the command that I want - but on a button in my Quick Access Toolbar.
upload_2017-8-6_10-31-27.png
 

Attachments

  • upload_2017-8-6_10-30-25.png
    upload_2017-8-6_10-30-25.png
    567.8 KB · Views: 13
ok, i've managed to use one of your earlier suggestions to solve the problem :)

here is the solution. thakns for your help.

Code: 
    Dim PfName As String
    Dim Pf As PivotField
   
    PfName = ActiveCell.PivotField.Name
   
    ActiveSheet.PivotTables(1).PivotFields(PfName).ShowDetail = False
 
You must log in or register to reply here.
Back
Top