We help IT Professionals succeed at work.

Why Can't I UNDO an action that a Macro has performed?

I ran a macro and wanted to undo it but the button was greyed out.

Why is it that I can't undo a macro action?
Comment
Watch Question

Michael FowlerSolutions Consultant

Commented:
When a macro is run it clears the undo buffer. While you can write macros to get around this, it is quite difficult and messy

Michael

Solutions Consultant
Commented:
I found these pages with examples on how it can be done if you are interested

http://www.j-walk.com/ss/excel/tips/tip23.htm

and

http://www.jkp-ads.com/articles/UndoWithVBA00.asp

One has to write code that:
    - Stores the previous state of anything that (possibly) needs to be undone
    - Sets a sub to be run when the user selects undo
    - Write that subroutine, which reverses the changes last made by your code.

Michael

Commented:
Not all macro actions will clear the Undo buffer; I once had your problem and was able to get to a satisfactory compromise by reducing the macro functionality and keeping only things that didn't break Undo (which was crucial for me). Basically, the macro actions that break Undo are the ones where Excel wouldn't have a notion on how to restore the previous state, because it hands control over to you and has no idea what you're doing.

About programmatically implementing Undo, see the solutions Michael gave above. They are generic (which is good), but if you find them complicated, consider that sometimes it is wiser to record just the tiny bits of information that get changed 99% of the time AND are crucial to you.

For example, on another sheet I copy a few columns from the main table onto a hidden column on the same line, every time there is change in that line. This can be used later to analyze edits. Not a proper Undo, but a decent solution for the purposes.