Jerry Paladino
asked on
VBA is Clearing Excel’s Undo Stack – In Reference to: Worksheet_PivotTableUpdate
When one of our Users updates a pre-written Excel OLAP Pivot Table report, we are using the VBA Event “Worksheet_PivotTableUpdat e” 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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Running vba will always clear the undo stack.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brad,
Thanks Again... I am having a little trouble getting the Worksheet_PivotTableBefore CommitChan ges 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
Thanks Again... I am having a little trouble getting the Worksheet_PivotTableBefore
Jerry