Stop undo stack from clearing

[ohiggs]

As you may already know, MS Excel clears the undo stack upon running a macro or executing code.

Does anyone know how to eliminate this or any work around?

Please chime in.

Thanks.

[rwgrietveld]

The work around is obvious to save before you run the macro.

This is what I picked up from the internet

This is the code to set the Undo command:
Application.OnUndo "Undo the ZeroRange macro", "undozero"
Where undozero is the name of the macro to be run which un-does the original macro, and the other bit is the text to show the user.

I haven't tried it. Have to investigate myself. It comes down to the fact that you can't undo a macro using normal Undo. What you can do is write a macro which undoes what the first one did and then use Application.OnUndo e.g.

[shg]

Running a macro does not intrinsically flush the undo stack; modifying a worksheet does.

There is no simple way to get around it. If you're not faint of heart, see http://j-walk.com/ss/excel/tips/tip23.htm.

[dominicb]

Good evening ohiggs

...and welcome to the forum!!

As you may already know, MS Excel clears the undo stack upon running a macro or executing code.
Does anyone know how to eliminate this or any work around?

You cannot eliminate this - only work with it. You will need to write your own custom undo routine, but it's not exactly straightforward. There are a couple of articles by John Walkenbach here and here about the subject and how to get around it.

There's also an article / tutorial here by Jan Karel Pieterse, along with a "one size fits all" type solution - the idea is you just copy his code into your application and it should do all the hard work for you.

HTH

DominicB