1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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"?

Discussion in 'VBA Macros' started by Gregg Wolin, Nov 7, 2018.

  1. Gregg Wolin

    Gregg Wolin New Member

    Messages:
    25
    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?
  2. Eloise T

    Eloise T Active Member

    Messages:
    812
    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.
  3. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,639
    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
  4. Gregg Wolin

    Gregg Wolin New Member

    Messages:
    25
    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?
  5. vletm

    vletm Excel Ninja

    Messages:
    4,299
    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.
  6. Debaser

    Debaser Active Member

    Messages:
    436
    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.

Share This Page