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

Preserving the Undo List

by Allen Wyatt
(last updated October 10, 2016)

3

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 choose the Undo command from the Edit menu or by using the Undo tool on the toolbar. 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, but the user, to "undo" 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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2060) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) 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

Can't Place Merge Field in Header Of a Catalog Merge Document

Word can perform several different types of mail merge operations, and the type you choose can affect how you are able to ...

Discover More

Changing a Toolbar Button Image

Changing the image of a button on a Toolbar in Word.

Discover More

Determining Business Quarters from Dates

Many businesses organize information according to calendar quarters, especially when it comes to fiscal information. ...

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 (menu)

Displaying the Selected Cell's Address

Need to know the address of the cell that is currently selected? There is no worksheet function to return this ...

Discover More

Finding the Last-Used Cell in a Macro

Ever wonder what the macro-oriented equivalent of pressing Ctrl+End is? Here's the code and some caveats on using it.

Discover More

Easily Adding Blank Rows

Want to add a bunch of blank rows to a your data and have those rows interspersed among your existing rows? Here's a ...

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 one more than 6?

2020-01-21 06:19:24

Özgür Yüksekdag

There is a way to use the built-in Undo-function to make Macro-Undos - for some changes:

If you only change cell values than don't change this directly like
cell(x,y).value = ....

Do something like
cell(x,y).select (or Range(x,y).select)
Application.SendKeys("...)

I was calculating some time values and entered them into the cells like:

ActiveCell.Value = CellTime + WorkTime <- No Undo available

Changed this to:

NewTime = CellTime + WorkTime

Application.SendKeys (Format(CellTime + WorkTime, "hh:mm~") <- Now Build-in Undo is available


Of course this is not the best solution for every uswcase. For completely automated marcos this would be worse. But there a backup of the workbook is better.

For macros that you use by commandbar to only change some cells and than work manually on the workbook, this is fine.


2019-10-22 13:53:54

gear

Hi, I am an auditor at an accounting firm. We use a vendor, Prosystems Fx Engagement, to handle our excel workbooks both in their 'audit binders' and also they include a lot of add-in macros. What they do mainly is manage and work with trial balances between workbooks in the binder.

Their code is open source. We can see it in the VBA editor and I have looked at it a little. Long story short, when I run their macros to manage trial balance or other processes, the undo list is preserved. What magic I guess do they possess that gives them the ability to not affect this? I want to do that kind of coding. I know they use VBA- at least to handle workbook events and it even interacts in some places with the Windows API through .dlls, but maybe their main workbook tools go through some other system I am not aware of- maybe like the .COM platform or the .NET Framework or ActiveX. I'd on't really know much about these things. I just heard what they are. I'm still interested, but somehow they preserve the undo function and I have never heard of that being possible, at least in VBA.


2016-11-05 15:17:33

A.Maurizio

Hello everyone my name is Maurizio and my problem is this: on an Excel sheet with office 2007 I put two 1 Buttons) Called the Undo and Redo Called. Now I want to create a function that will give me the opportunity to worksheet level, That whenever I press one I have the other key, they behave exactly as it behaves any application of "return to the word I wrote earlier) Number in that particular cell. How do I create everything. Thanks for all the help that you can offer me concerning, sincere greetings from a. Maurizio


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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.