locked
Why does Excel not allow "Undo" after a VBA execution? RRS feed

  • Question

  • I've read a lot on this topic, but nothing really explains why the "UNDO" option doesn't work after a vba code has been executed. Does anyone have anything to can offer about this?

    Is there any plans to allow "undo" after vba code has been executed?

    thx for any response!

     

    Friday, April 22, 2011 2:47 PM

Answers

  • As you say the Undo stack is cleared when VBA code (or any other code) makes any change to the interface.

    When changes are made directly in the Excel UI temporary snap shots are stored of changes which can be rolled back in reverse order required to the limit of Undos. Excel has no idea how any programmatic change might relate to its own Undo history.

    Say user changes A1 on Sheet1, its stored in the Undo history
    VBA deletes Sheet1
    User wants to Undo his change to A1 !

    Is there any plans to allow "undo" after vba code has been executed?

    I'm not aware of any plans and it would be a major undertaking to cater for that

    Depending on what your code does there are various things you might be able to do to mitigate for loss of Excel's Undo

    Peter Thornton

    • Marked as answer by Bruce Song Thursday, May 5, 2011 10:42 AM
    Friday, April 22, 2011 3:26 PM
  • Create your own UNDO facility.

    Use a separate UNDO workbook as trying to create a copy of a worksheet in the same workbook is definitely problematic when using range names.

    Use a numbering scheme to create multiple backups of the worksheet in question.

    Watch for that 32 character name limitation on worksheets.

    • Marked as answer by Bruce Song Thursday, May 5, 2011 10:42 AM
    Tuesday, May 3, 2011 1:30 PM
  • Hi Gone2TheDogs,

    I've read a lot on this topic, but nothing really explains why the
    "UNDO" option doesn't work after a vba code has been executed. Does
    anyone have anything to can offer about this?

    As you have experienced, VBA can zap the Undo stack.
    I demonstrate one way to build your own undo history in a VBA routine. A lot of hard work though:

    www.jkp-ads.com/articles/undowithvba00.asp


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    • Marked as answer by Bruce Song Thursday, May 5, 2011 10:42 AM
    Tuesday, May 3, 2011 2:33 PM

All replies

  • As you say the Undo stack is cleared when VBA code (or any other code) makes any change to the interface.

    When changes are made directly in the Excel UI temporary snap shots are stored of changes which can be rolled back in reverse order required to the limit of Undos. Excel has no idea how any programmatic change might relate to its own Undo history.

    Say user changes A1 on Sheet1, its stored in the Undo history
    VBA deletes Sheet1
    User wants to Undo his change to A1 !

    Is there any plans to allow "undo" after vba code has been executed?

    I'm not aware of any plans and it would be a major undertaking to cater for that

    Depending on what your code does there are various things you might be able to do to mitigate for loss of Excel's Undo

    Peter Thornton

    • Marked as answer by Bruce Song Thursday, May 5, 2011 10:42 AM
    Friday, April 22, 2011 3:26 PM
  • Hi Gone2TheDogs,

    Have you resolved your issue yet, and does the suggestion help you? If you have any concern on the thread, feel free to follow up.

    Best Regards,



    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, April 26, 2011 8:32 AM
  • Create your own UNDO facility.

    Use a separate UNDO workbook as trying to create a copy of a worksheet in the same workbook is definitely problematic when using range names.

    Use a numbering scheme to create multiple backups of the worksheet in question.

    Watch for that 32 character name limitation on worksheets.

    • Marked as answer by Bruce Song Thursday, May 5, 2011 10:42 AM
    Tuesday, May 3, 2011 1:30 PM
  • Hi Gone2TheDogs,

    I've read a lot on this topic, but nothing really explains why the
    "UNDO" option doesn't work after a vba code has been executed. Does
    anyone have anything to can offer about this?

    As you have experienced, VBA can zap the Undo stack.
    I demonstrate one way to build your own undo history in a VBA routine. A lot of hard work though:

    www.jkp-ads.com/articles/undowithvba00.asp


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    • Marked as answer by Bruce Song Thursday, May 5, 2011 10:42 AM
    Tuesday, May 3, 2011 2:33 PM