Expand All/Collapse All

[#1]

Hello,

I am using Pivot table.

I want to place a button labelled 'Expand All/ Collapse All' and on click of it all the columns of the Pivot table should expand or collapse.

I dont want to use the 'Always Fully Expanded' property.

[#2]

Hi You can use Marco to do this. Rough algorithm.

sub Toggle

     set obj = ActiveDocument.GetSheetObject("<ObjectName>")

     set objField=obj.GetField

     set objProperty = objField.GetProperties

/*     Here you set the property on button click.. you need to use one variable to toggle.*/

     if(Expanded)

          Then Collapse

     if(Collapsed)

          then Expand

end sub

[#3]

Hi KStreak,

But what statements to write to expand and collapse?

[#4]

any help?

[er_mohit]

Right click on chart here you got option Expand All Or Collapse All

hope it helps

[#6]

Hi Mohit,  Thanks for quick reply.  But I need it on button click. So I need a macro which will expand or collapse on button click.  I have used following script: Function CollapseExpandPivot ("CH01") set chart = ActiveDocument.GetSheetObject(objectID)      set ch=chart.GetProperties      if ch.TableProperties.PivotAlwaysFullyExpanded =false then           ch.TableProperties.PivotAlwaysFullyExpanded=true           chart.SetProperties ch      else           ch.TableProperties.PivotAlwaysFullyExpanded=false           chart.SetProperties ch      end if end Function   Expand is working fine using above code.  But when I collapse, I want it always to collapse till 2 columns. I mean on collapse 1st 2 columns should always remain expanded.  Is this possible?

[#7]

Hi,

this is supposed to do it :

for n = 0 to 5 ' number of dimensions was 5

set chart = ActiveDocument.GetSheetObject("CH01")

chart.ExpandLeft 0, n, true

next

But apparently it dosn't work in recent QV version, so I suppose it's not possible to do it any more.

[#8]

yes I had tried this and it does not work in 11 version.  any other solution?