Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have pivot chart that looks like this. QV seems to remember the last "state" I was in.
My question is how do I collapse all the dimensions back to the "FiscalYear" level (see 2nd screenshot)
every time I click on this sheet? I don't want the "state: to be remembered.
Thank you.
Just figured out why my code wasn't working. I had only one variable to control the label of the button to say
"Expand" or "Collapse" and I'm using this variable to determine whick BM to run. Apparent QV doesn't like this.
So I created a second variable to control the BM, now the IF() statement works. Wow! Everyday is learning day!!
Hi,
Try with below option
Presentation Tab -> Uncheck Always Fully Expanded check box.
Hi,
try sheet properties -> Trigger
The "Always Fully Expanded" was already unchecked.
Thomas,
Adding a trigger to minimize the object simply minimizes my chart when I click on the sheet. So this is not what I want.
I just wanted to collapse the dimensions.
Hi Sydney,
Try the below macro. It will expand your pivot permanently. i am working on to make it more flexible.
run this macro on sheet open trigger.
Sub ExpandChart2
On Error Resume Next
set vUN = ActiveDocument.Variables("vUserName")
set vFilePath = ActiveDocument.Variables("vFilePath")
Set vChartToExpand = ActiveDocument.Variables("vChartToExpand")
Set chart = ActiveDocument.GetSheetObject(vChartToExpand.GetContent.String)
Set gp = chart.GetProperties
gp.TableProperties.PivotAlwaysFullyExpanded = TRUE
chart.SetProperties gp
set obj = ActiveDocument.GetSheetObject("CH01")
End Sub
Regards
KC
Hi Jyothish,
Thanks for the code. But I'm looking to collapse the dimensions (again, see 2nd screenshot) and not
to expand them. Do you have code to do "collapsing"? Thanks!
Try this through AccessPoint every time you login
I found this post but it's a user driven action.
Expand all the dimensions - Pivot Table
Right click on dimension -> choose expand / collapse.
Anyone knows the code/macro behind this event?
Does this only affect my account? What about the users?