Subscribe in a readerCreating 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
All comments about this page:
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....
Comment by: Valdek (4/5/2009 12:13:36 PM)Thanks Jan for excellent code piece.
Working on assume quite useful keyboard shortcuts like align center, align left, align top, paste formats etc. for Excel 2003.
Tried to use it to undo pastespecial command implemented from VBA but seems to me works only on commands with properties having definite value, i.e. align commands - works OK, pastespecial - NO, Merge/Unmerge - NO .
Pastespecial Values could handle by modifying code suggested by Vyacheslav (this code did not take into account possible differences in copy from and copy to range sizes).
Pastespecial formats - this is beyond my timelimit can spend to my hobbyprogramming.
It is a pity of course that Microsoft has not thought of easy shortcut creation routines with full funcionality for most used commands.
Any suggestions on making your code to work with pastespecial ?
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.
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.