Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Preserving the Undo List.

Preserving the Undo List

by Allen Wyatt
(last updated May 9, 2016)

8

As you are working in Excel, there is a bit of a "safety net" in place in regards to changes you make. Most people know that if they mess things up, they can quickly press Ctrl+Z or click the Undo tool at the upper-left of the program window. If you don't like what you just did, you can easily undo it and get back to the way things were before.

When you run a macro, however, the macro doesn't "play nice" with the Undo list. In fact, running a macro completely erases the Undo list, and therefore you cannot automatically undo the effects of running the macro. There is no intrinsic command—in Excel or in VBA—to preserve the Undo list. There are a couple of ways that you can approach the problem, however.

If you feel that you might want to undo the effects of a macro, the first thing you can do is to save your workbook before running the macro. This, in effect, gives you a "pre-macro" version of the workbook. If you want to later revert to this version, simply close the workbook without saving and then reload it from disk.

Another option is to rethink the way you do your macros. If you have a macro that does a lot of processing of information in your worksheet, code the macro so that it maintains, in memory, the state of anything that it changes. You can then create a separate macro that reads this information and effectively undoes the effects of the first macro.

To make this approach really handy, the last step in your primary macro can be to "stuff" information on the Undo stack. This info can then be used, by the user, to "undo" the macro that you created. For instance, the following macro command could be the last one in your primary macro:

Application.OnUndo "Primary Macro", "UndoPrimary"

After this command, when the user looks at the Undo list, he or she will see the text "Primary Macro." If they choose this option from the Undo list, then your "undo" macro (UndoPrimary) is executed.

You should note that this approach doesn't save what was on the Undo list before you ran the macro—there seems no way to do that. When your primary macro is through running, there will only be a single option available on the Undo list: Primary Macro.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12513) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Preserving the Undo List.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Importing a Text File and Inserting after a Bookmark

Word macros are a great way to automate some of the ways in which you create documents. If you have a need to insert the ...

Discover More

Transposing Table Contents

When you transpose information, it is essentially "rotated" in a direction. If you transpose the information in a table, then ...

Discover More

Strip Trailing Spaces

If you get tired of documents that always seem to have extra spaces at the end of lines, here's a quick way to get rid of ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Removing a Macro from a Shortcut Key

When you assign a macro to a shortcut key, you make it easy to run the macro without ever removing your hands from the ...

Discover More

Selecting a Cell in the Current Row

Macros often need to select different cells in a worksheet. Here's how you can use macro commands to change which cell is ...

Discover More

Using Seek In a Macro

When reading information from a text file, your macro may need to start reading at a place other than the beginning of the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 9?

2017-07-24 12:28:23

sludge7051-x

I'm using Excel 2010

' THERE IS NO "UNDO" FOR MACROS
' By design, there is no "Undo" for Macros. How do you handle it if there's a problem?
' Previously, I was thinking, just be prepared to Close without Saving changes, and re-open the original worksheet.
' But, you could lose a lot of work that way, so . . .
' Click Save frequently, or after any large quantity of data entry - But what if you didn't do that, before running a macro that went wrong?
' So, the answer is to have the macro save the worksheet before it runs, as seen in the following command . . .
ActiveWorkbook.save

QUESTION
' Should I un-check these? ie. Is it auto-saving my worksheet, or, are these only coming into effect if Excel crashes?
' File / Options / Save / [un-check] Save AutoRecover information every 10 minutes
' File / Options / Save / [un-check] Keep the last autosaved version if I close without saving


2016-05-10 10:40:53

Gary Lundblad

Gotcha- thank you Matt!

Gary


2016-05-10 02:24:38

Thomas Papavasiliou

A safe way can also be to create a copy of the worksheet and run the macro on the copy.
This procedure can be embedded in the macro and keep your original sheet intact.

The only drawback is that when you are pleased with the result, you have to erase and probably rename some sheets manually.


2016-05-09 20:00:18

Matt H

Gary - you need to code a macro called "UndoPrimary" where you actually undo all the steps performed in the Primary macro. It sounds like you might be missing this step?


2016-05-09 11:09:24

Gary Lundblad

I like the idea of this macro addition; however; it doesn't seem to be working. I recorded a very simple macro, then stepped into it and added this piece of code to the of the macro. Then I ran the macro, but when I went to undo it, although I could see the "Undo Primary" there as the only undo option, when I clicked it I got an error that said "Cannot run the macro '\......' The macro may not be available in this workbook or all macros may be disabled." Clearly the macro is available in this workbook, since it ran in the first place, and clearly all macros are not disabled, since it ran in the first place. Ideas??

Thank you!

Gary


2016-05-09 09:01:38

Kevin

And hopefully tomorrows tip will be for "code the macro so that it maintains, in memory, the state of anything that it changes."


2016-05-09 08:16:55

Tim

I haven't tried this out yet, but this could be immensely useful. I have a lot of "quick and dirty" VBA scripts that use relative cell references. I've accidentally run the macro from the wrong cell or worksheet more times than I care to admit!

Thank you very much!


2016-05-09 06:55:15

Evan

Need info on this


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.