Creating An Undo Handler To Undo Changes Done By Excel VBA

Conclusion

I have demonstrated a method to add an undo handler to your project. Of course implementing this method means you will have to change existing code that performs actions that may need to be undone later.

The actions the undo handler can undo are limited to changes to properties of objects in general. Things like inserting or deleting sheets, refreshing querytables, updating Pivottables and etcetera, cannot be undone using this technique.

Download

I have prepared a download for you with example code. Find it here.

Feedback

Since you have managed to reach the end of this article, maybe you would care to write me a small message, expressing your opinion on this article?

Click here to write an email message to me.

Comments

Showing last 8 comments of 19 in total (Show All Comments):

Comment by: Jan Karel Pieterse (4/6/2009 12:51:19 AM)

Hi Valdek,

Unfortunately not, since you never know what exactly is being pasted and how many cells are affected.
It is doable, but quite a lot of work.

NB: If you use a different approach to action the stuff you are trying to put in kb shortcuts you can keep the undo stack.

For example, there are existing toolbar buttons to do paste formats and paste values. There is also one for the paste special dialog. Put those controls on a toolbar and you can use Application.commandbars("Foo").Controls("Bar").Execute in your code.

Comment by: Manoj D Kapashi (11/10/2010 6:14:23 AM)

I am trying to stop a workbook from closing or saving if certain conditions are not fulfilled, and force it to remain open.

Is there some way to do this? I tried to use undo for workbook_before_close and workbook_before_save, but obviously it is not that simple.

Can you pl help?

Comment by: Jan Karel Pieterse (11/10/2010 6:43:37 AM)

Hi Manoj,

I kindly request you to go here to ask questions like that:

http://www.eileenslounge.com

There are a lot of people there to help you.

Comment by: James007 (1/15/2011 4:19:16 AM)

Hi Jan Karel,

Would it be possible to events such worksheet_change() trapped in a similar way to benefit from your Undo procedure ?

Thanks in advance for your kind assistance
Cheers
James

Comment by: Jan Karel Pieterse (1/15/2011 11:14:41 AM)

In principle, yes. This method can be used from any procedure.

Comment by: Sir Shagsalot (1/26/2011 8:46:14 PM)

Hi,

First, this undo stuff is a lifesaver for someone like me, who has other things he prefers to do to writing code... :-)

Shame it requires us to actually protocol along every action we do (it also has advantages, but still).

Ideally one would re-structure it so that one can just turn undo for VBA on for a given worksheet or workbook and the code would do the rest.

Maybe use dynamic code insertion into the onchange event of the workbook/worksheet to protocol the change, so we can always restore the worksheet to the "before" state?

Not sure. If I ever find enough time to do some coding instead of my other interests I may try it. Will report back if I do...

Greez SSAL

"Die gefährlichste Weltanschauung ist die Weltanschauung derjenigen, die die Welt nicht angeschaut haben."
(Alexander von Humboldt)

Comment by: Chris Slowik (10/19/2011 12:38:13 PM)

Great bit of code here.. I'm busy implementing it in a suite of UDFs that i've distributed to my coworkers.

One thing though, I noticed that this clears the undo stack of anything you did before the action. Is there any way around this? Is there a way to modify this code so it just adds on to the existing undo stack?

Comment by: Jan Karel Pieterse (10/19/2011 11:20:42 PM)

Hi Chris,

No, unfortunately you cannot keep the current Undo stack. As soon as you do anything significant in VBA, the Undo stack is erased.

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.