• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Settings or syntax to preserve "Undo"?

Gregg Wolin

Member
I know that the ability to "undo" is purged after excel runs macros. What I don't understand is if my macros are being triggered by value changes to a specific range, [i.e. Sub Worksheet_Change(ByVal Target As Range) ]why is the undo cache being purged when i change things outside of the trigger range?
 
I will be watching this thread as I have wondered the same thing...specifically "Why is the undo cache purged when running a macro?"

I would say, "Ask Microsoft" but you'd have more success getting blood out of a turnip.
 
Gregg, Eloise

Clearing the undo stack is an unfortunate side effect of running VBA code. You can't prevent it. The simple use of the Worksheet_Change event is running a macro.

If you are really keen on maintaining the Undo stack have a read of:
https://www.jkp-ads.com/Articles/UndoWithVBA00.asp

Hui - How did I know that you would have the answer (even if its not the one I wanted)? What I am trying to wrap my brain around is that if the macro was triggered by pressing a button, I could undo to my heart's desire. Why is clicking a button any different than changing the number in a cell? Is there a way to simply toggle "turn off" all the vba in my workbook on and off whilst i fiddle with it?
 
Gregg Wolin
As You have learnt how would Excel do ...

Could this be like:
Measure twice - cut once
Before press/push something - think
Prevent:
Some of those would prevent to use line
Application.EnableEvents = False
to prevent ... and allow again ..
Application.EnableEvents = True

... check in the begin of 'Sub', is EnableEvent 'true' or 'false'
and Exit that 'Sub' if it's 'false'.

You can set that EnableEvent to 'True or False' with button ...
but You should remember to change it back as needed.
 
If your code doesn't do anything besides checking if the target is in a particular location, then changing a cell outside that location should not clear the undo stack.
 
Back
Top