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.

Rate this article

(Rated: 35 times, average rating: 5.4)

Comments

All comments about this page:

Comment by: Jan Karel Pieterse (3/9/2006 8:37:59 AM)

Hi DM, Good suggestions!

Comment by: Vyacheslav Maliuhin (3/2/2007 5:55:22 AM)

Thank you very much for your tutorials! But i think, this UNDO implementation best suits for large projects. Here is a tutorial for small projects:
http://j-walk.com/ss/excel/tips/tip23.htm

Comment by: Jan Karel Pieterse (3/2/2007 7:31:57 AM)

Hi Vyacheslav,

I agree with that. And thanks for the link.

Comment by: Jac (12/6/2007 2:09:52 AM)

Hi,

I can't seem to make the UNDO work for Conditional Formatting.

It can restore the previous formatting but the formula for the Conditional Formatting still remains.

Anyone knows how to use VBA to add conditional formatting and use this class to undo?

Comment by: Jan Karel Pieterse (12/6/2007 3:12:30 AM)

Hi Jac,

Conditional formatting expects you to enter the formula in Excel's locale NOT in US syntax.

So with my regional settings (list separator = ;)

This line works:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=sum(a1;a2)=1"

But this one fails:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=sum(a1,a2)=1"

So: The Formula1 (and 2) property of a formatcondition object reads AND writes LOCAL formula syntax in VBA.

This is opposed to the Formula1 and Formula2 properties of the VALIDATION object: those SHOW FormulaLocal, but if you want to set them, you must use the US formula syntax.

Comment by: So Theara (4/10/2008 3:04:38 AM)

Hi! I saw ur code about undoExcelVba. But I wanna be to see the implementation about Undo&Redo use in Access VBA. Can you shown it?.
Thanks...

Comment by: Jan Karel Pieterse (4/10/2008 5:23:16 AM)

Hi So,

I'm sorry, that is beyond my expertise.

Comment by: andrewit (6/27/2008 1:46:39 AM)

I suggest you should add command to save the file as a temporary file and then run
the Application.Onundo

It just made it easy....

Add a comment too!!!

Please enter your comments about this tool below.