Capturing UNDO Stack

[herm5263]

I’m looking for method to save the Undo stack before running VBA. I have seen a few examples of VBA that can undo effects of VBA routines (e.g., at j-walk /ss/excel/tips/tip23.htm, and at jkp-ads /articles/undowithvba00.asp). I wrote a sub to track changes to Excel 2007 worksheets by trapping on Workbook_SheetChange event. Unfortunately, w/o doing something really clever, the Undo stack gets cleared as soon the sub runs (user edits any value or formula) which means that user Never has access to correct undo mistakes via Ctrl-Z. On one thread in forum, ‘xld’ says “Unfortunately, MANY [my emphasis] macro commands clear the undo stack, so it is lost.” So, is there hope that some commands will preserve the stack?

[mikerickson]

Rather than capture the UnDo stack, you can use the Application.OnUndo to add to it. (i.e. create an UnDo for your macro). This will leave the remainder of the stack untouched.

Type "xyz"
Run yourMacro
Undo Run yourMacro
Undo Type "xyz"

[herm5263]

Read text on Application.Undo in Excel VBA Help. (MS Help isn't very good.) Still not sure how to preserve user edits before executing macro. Here's first attempt which wipes out Undo stack and replaces with text "Undo VBA Proc".

Sub TestUndo()
' test code to save Undo stack
' Type "xyz"
' Run yourMacro
Application.OnUndo "Undo VBA Proc", "Do_Nothing"
Debug.Print "TestUndo" & Now
' Undo Run yourMacro
' Undo Type "xyz"
End Sub
Sub Do_Nothing()
End Sub

[mikerickson]

From my testing OnUndo doesn't nessesarily preserve the stack. It provides for an Undo for the Macro, but the stack below that is lost.