Solved

VBA is Clearing Excel’s Undo Stack – In Reference to:  Worksheet_PivotTableUpdate

Posted on 2014-04-29
5
800 Views
Last Modified: 2014-04-29
When one of our Users updates a pre-written Excel OLAP Pivot Table report, we are using the VBA Event “Worksheet_PivotTableUpdate” to kick off a procedure that writes a Usage Log record to SQL Server.   When the PivotTableUpdate event runs, it clears Excel’s UNDO stack and the user does not have the ability to Undo the pivot table update.   I am looking for a solution that will allow the VBA Usage Log routine to run but still allow for the Pivot update to be reversed with Undo.

One available option is to eliminate running the PivotTableUpdate Event but we would like to run our Usage Log routine if we can find a way around this current hurdle.

Thank you for any suggestions and assistance,
Jerry
0
Comment
Question by:Jerry Paladino
  • 2
  • 2
5 Comments
 
LVL 81

Assisted Solution

by:byundt
byundt earned 500 total points
ID: 40030372
As it happens, the Undo stack is cleared by design whenever a VBA macro runs. This is a "feature" and not a "bug".

The best way around it would probably be the following:
1.  Use the event sub to turn events off, capture the current state of the PivotTable, use Application.UnDo and then capture the previous state of the PivotTable in a Public array variable, then restore the PivotTable and perform your logging. Finally, turn events back on.
2.  If the user wants to Undo the update, he needs to run a separate macro that applies the previous state from the Public array variable.

Potential problems: the PivotTable formatting won't be captured in the Public array variable, so the post-restoration results may look different--though they will have the desired values.

Note: to declare a variable as Public, you do so before any of the subs or functions in the module sheet. I suggest making that declaration in a regular module sheet so the variable is readily available to both event subs and regular subs.
Public PivotTableUndo As Variant

Open in new window

0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40030382
Running vba will always clear the undo stack.
0
 
LVL 16

Author Comment

by:Jerry Paladino
ID: 40030464
Thank You Brad...   Yes, I understand this is a VBA feature and did not view it as a bug.   Was just looking for a way to accomplish both if possible.  

Something like intercepting the Pivot Table call before it updated the pivot table, call the Usage Log routine prior to the Pivot Update and then let the pivot table update.   That would complete the VBA before the pivot updated and should leave the Undo stack in place to return it to original state.   I know there is not one, but something like a BeforePivotTableUpdate event.  Could that be done in a class module?

I understand and appreciate your suggestion on capturing the state of the pivot table and then putting it back together after the update.   The formatting is one issue and would require a lot of code to put things back to original state for 20+ standard reports. The other is the time required to process the OLAP report again on rebuild.

Thanks,
Jerry
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 40030544
Jerry,
Excel 2013 has six different PivotTable event subs available to worksheet code panes. You are using the last to occur in the sequence. I haven't ever used it, but PivotTableBeforeCommitChanges is supposed to run before changes are committed to an OLAP server, and so may be able to substitute for the non-existent "PivotTableBeforeUpdate".

Brad
0
 
LVL 16

Author Closing Comment

by:Jerry Paladino
ID: 40030762
Brad,

Thanks Again...   I am having a little trouble getting the Worksheet_PivotTableBeforeCommitChanges Event to fire so I'll do a bit more research on that one.   Between the two options you provided I can make this work.   I appreciate your help.

Jerry
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
splitting text of cell to columns 14 22
Fixing a embedded format 7 29
Merging-Splitting-Multiple-Rows 33 40
MS Excel IF AND OR statement 3 21
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now