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).
Last edited by rasonline; 08-06-2017 at 02:39 AM.
Try something like this (not tested).
You may find this link helpful:
Please Login or Register to view this content.
Referencing an Excel Pivot Table Range using VBA
Last edited by AlphaFrog; 08-01-2017 at 11:40 AM.
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
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
Please Login or Register to view this content.
note that this line of code was also generating an error, so I quoted it out
Please Login or Register to view this content.
What exactly are you selecting before you run the macro? Your description of events is confusing and sometimes contradictory.
Originally Posted by 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)
The pivot table display is Classic view, so each field is in a separate column.
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.
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.
Ok - i've managed to solve it. Here is the solution for anybody else who wants it.
Please Login or Register to view this content.
Cross-Post at this link
http://chandoo.org/forum/threads/exp...eld-vba.14326/
< ----- Please click the little star * next to add reputation if my post helps you
Visit Forum : From Here
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
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thanks, noted and will apply for future reference.
I think you could shorten it to this for what it's worth.
ActiveCell.PivotField.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.Ok - i've managed to solve it. Here is the solution for anybody else who wants it.
Please Login or Register to view this content.
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".
There is always a better way.
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.
Please Login or Register to view this content.
_________________________________________________________________________________________________________
Ideally, I want to create a toggle button like this (as opposed to the standard separate expand and collapse buttons):
Any suggestions?Please Login or Register to view this content.
Last edited by rasonline; 03-08-2024 at 07:59 AM.
The thread is marked as solved - remove that if you want further assistance.
thanks for that. Now updated thread title to remove "solved"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks