I have been working on a problem like this myself the last couple of days, and have made a one level undo routine that can undo the user's change that triggered the Worksheet_Change event. Maybe this can help you.
When writing my own UNDO procedure, I only get *half* of an undo: I can undo my own VBA code's work, but I cannot undo the user's change which triggered it. Is my understanding here correct?
I have a range called "rngInput" in Worksheet(1).
I use Worksheet(3) to store a undo backup.
The way it works is that I store a snapshot of "rngInput" values in an array. Then do Application.Undo just to get a "picture" of what I have to restore if the user wants to do a undo later. This is copied to my backupsheet. I then put the values i stored in my array back into "rngInput", to get back to the state I had before i did to undo.
This code is run from the Worksheet_Change event before I run my routine that handles the formatting.
Here is the code:
Here is my routine that actually performs the undo action when the users requires it:
I have made a Command button on the worksheet the users can use when they wants to do the undo action. The used can have made changes to the worksheet outside the "rngInput" range, and this would not have triggered any VBA that flushed the Undo buffer. The command button then uses the ordinary undo routine. If the undo buffer is empty, my custom routine is run.
Here is the code for the button:
Running the CommandButton1_Click routine would normally flush the undo stack before you can do an Application.Undo , but if you set the TakeFocusOnClick property to False, the stack is not flushed.
My undo code only takes care of the values. This is enough for me because the formatting and datavalidation is already taken care of in the routines that required me to make this undo routine in the first place. I think this might be the same in your case.
Hope this can be to any help.