Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. 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.
Written by Allen Wyatt (last updated May 4, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
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.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12513) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Preserving the Undo List.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Excel allows you to fill a cell's background with just about any color you want. If you need to determine the RGB value ...
Discover MoreYou may need to automatically generate unique numbers when you create new worksheets in a workbook. Here are a couple of ...
Discover MoreWorkbooks get corrupted from time to time; that's a fact of life in an Excel world. If those corrupted workbooks contain ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-12-16 10:50:53
J. Woolley
The Tip says, "running a macro completely erases the Undo list." This is not always the case. For example, the Undo and Redo lists are not disturbed by this macro:
Sub ThisMacro()
MsgBox "You entered " & InputBox("Enter something:")
End Sub
According to Microsoft, the Undo (Ctrl+Z) stack is usually cleared when a macro or event handler completes, but only if it changes the Excel "environment" as indicated below:
+ Change a cell's value
+ Insert, delete, or format cells on the spreadsheet
+ Move, rename, delete, or add sheets to a workbook
+ Change an environment option, such as calculation mode or screen views
+ Add names to a workbook
+ Set properties or execute most methods
In my experience, the last item should be qualified: "Depending on the property or method."
A user-defined function (UDF) usually does not clear the Undo stack because it is prohibited from changing the Excel "environment" (which is not clearly defined).
2024-05-04 11:16:44
J. Woolley
For an example of code that incorporates Application.OnUndo, see my comment dated 2024-05-03 here: https://excelribbon.tips.net/T006126
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments