Most Valuable Professional

View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Articles > Undo With Excel VBA > Conclusion
Deze pagina in het Nederlands

Creating An Undo Handler To Undo Changes Done By Excel VBA


I have demonstrated a method to add an undo handler to your project. Of course implementing this method means you will have to change existing code that performs actions that may need to be undone later.

The actions the undo handler can undo are limited to changes to properties of objects in general. Things like inserting or deleting sheets, refreshing querytables, updating Pivottables and etcetera, cannot be undone using this technique.


I have prepared a download for you with example code. Find it here.


Since you have managed to reach the end of this article, maybe you would care to write me a small message, expressing your opinion on this article?

Click here to write an email message to me.



All comments about this page:

Comment by: DM Unseen (9-3-2006 06:25:32)

Nice One JK!

Some comments

mUndoClass.AddAndProcessObject ActiveSheet.Cells(i,1),"Interior.Colorindex", 15

Could also be

mUndoClass.AddAndProcessObject ActiveSheet.Cells(i,1).Interior,"Colorindex", 15

because most properties return objects.

In general if you have property assignment:

object1.object2.object3.property1 = <XYZ>

you could parse this as

mUndoClass.AddAndProcessObject object1.object2.object3,"property1", <XYZ>

although controlling where you split your object and property is good, maybe a function that parses it would be handier:

mUndoClass.AddAndProcessAssignment "ActiveSheet.Cells(i,1).Interior.Colorindex = 15"


mUndoClass.AddAndProcessAssignment "ActiveSheet.Cells(i,1).Interior.Colorindex" ,15

Could work. You would need to extract the property name(and maybe value) by searching for the dot. This can give issues in some circumstances (i.e. default properties), but it would be easier to code with.


Comment by: DM Unseen (9-3-2006 06:30:22)

I also see that you can only process property assignments that are not objects!


Comment by: Jan Karel Pieterse (9-3-2006 08:37:59)

Hi DM, Good suggestions!


Comment by: Vyacheslav Maliuhin (2-3-2007 05:55:22)

Thank you very much for your tutorials! But i think, this UNDO implementation best suits for large projects. Here is a tutorial for small projects:


Comment by: Jan Karel Pieterse (2-3-2007 07:31:57)

Hi Vyacheslav,

I agree with that. And thanks for the link.


Comment by: Jac (6-12-2007 02:09:52)


I can't seem to make the UNDO work for Conditional Formatting.

It can restore the previous formatting but the formula for the Conditional Formatting still remains.

Anyone knows how to use VBA to add conditional formatting and use this class to undo?


Comment by: Jan Karel Pieterse (6-12-2007 03:12:30)

Hi Jac,

Conditional formatting expects you to enter the formula in Excel's locale NOT in US syntax.

So with my regional settings (list separator = ;)

This line works:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=sum(a1;a2)=1"

But this one fails:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=sum(a1,a2)=1"

So: The Formula1 (and 2) property of a formatcondition object reads AND writes LOCAL formula syntax in VBA.

This is opposed to the Formula1 and Formula2 properties of the VALIDATION object: those SHOW FormulaLocal, but if you want to set them, you must use the US formula syntax.


Comment by: So Theara (10-4-2008 03:04:38)

Hi! I saw ur code about undoExcelVba. But I wanna be to see the implementation about Undo&Redo use in Access VBA. Can you shown it?.


Comment by: Jan Karel Pieterse (10-4-2008 05:23:16)

Hi So,

I'm sorry, that is beyond my expertise.


Comment by: andrewit (27-6-2008 01:46:39)

I suggest you should add command to save the file as a temporary file and then run
the Application.Onundo

It just made it easy....


Comment by: Valdek (5-4-2009 12:13:36)

Thanks Jan for excellent code piece.

Working on assume quite useful keyboard shortcuts like align center, align left, align top, paste formats etc. for Excel 2003.

Tried to use it to undo pastespecial command implemented from VBA but seems to me works only on commands with properties having definite value, i.e. align commands - works OK, pastespecial - NO, Merge/Unmerge - NO .

Pastespecial Values could handle by modifying code suggested by Vyacheslav (this code did not take into account possible differences in copy from and copy to range sizes).

Pastespecial formats - this is beyond my timelimit can spend to my hobbyprogramming.

It is a pity of course that Microsoft has not thought of easy shortcut creation routines with full funcionality for most used commands.

Any suggestions on making your code to work with pastespecial ?


Comment by: Jan Karel Pieterse (6-4-2009 00:51:19)

Hi Valdek,

Unfortunately not, since you never know what exactly is being pasted and how many cells are affected.
It is doable, but quite a lot of work.

NB: If you use a different approach to action the stuff you are trying to put in kb shortcuts you can keep the undo stack.

For example, there are existing toolbar buttons to do paste formats and paste values. There is also one for the paste special dialog. Put those controls on a toolbar and you can use Application.commandbars("Foo").Controls("Bar").Execute in your code.


Comment by: Manoj D Kapashi (10-11-2010 06:14:23)

I am trying to stop a workbook from closing or saving if certain conditions are not fulfilled, and force it to remain open.

Is there some way to do this? I tried to use undo for workbook_before_close and workbook_before_save, but obviously it is not that simple.

Can you pl help?


Comment by: Jan Karel Pieterse (10-11-2010 06:43:37)

Hi Manoj,

I kindly request you to go here to ask questions like that:

There are a lot of people there to help you.


Comment by: James007 (15-1-2011 04:19:16)

Hi Jan Karel,

Would it be possible to events such worksheet_change() trapped in a similar way to benefit from your Undo procedure ?

Thanks in advance for your kind assistance


Comment by: Jan Karel Pieterse (15-1-2011 11:14:41)

In principle, yes. This method can be used from any procedure.


Comment by: Sir Shagsalot (26-1-2011 20:46:14)


First, this undo stuff is a lifesaver for someone like me, who has other things he prefers to do to writing code... :-)

Shame it requires us to actually protocol along every action we do (it also has advantages, but still).

Ideally one would re-structure it so that one can just turn undo for VBA on for a given worksheet or workbook and the code would do the rest.

Maybe use dynamic code insertion into the onchange event of the workbook/worksheet to protocol the change, so we can always restore the worksheet to the "before" state?

Not sure. If I ever find enough time to do some coding instead of my other interests I may try it. Will report back if I do...

Greez SSAL

"Die gefährlichste Weltanschauung ist die Weltanschauung derjenigen, die die Welt nicht angeschaut haben."
(Alexander von Humboldt)


Comment by: Chris Slowik (19-10-2011 12:38:13)

Great bit of code here.. I'm busy implementing it in a suite of UDFs that i've distributed to my coworkers.

One thing though, I noticed that this clears the undo stack of anything you did before the action. Is there any way around this? Is there a way to modify this code so it just adds on to the existing undo stack?


Comment by: Jan Karel Pieterse (19-10-2011 23:20:42)

Hi Chris,

No, unfortunately you cannot keep the current Undo stack. As soon as you do anything significant in VBA, the Undo stack is erased.


Comment by: Alexander (9-5-2012 13:13:46)

Hi Jan Karel
I'm not sure if this is what I need to accomplish my needs. I am trying to implement a “Undo” functionality for macro changes. Currently Excel does not allow this. Your help in this is most appreciated.


Comment by: Jan Karel Pieterse (9-5-2012 23:17:14)

Hi Alexander,

Well, this is one way of implementing what you need. All I can say is that undoing VBA changes will be something you have to program yourself.


Comment by: Keith Lee (7-8-2012 22:49:53)

Hi Alexander,

I am a very NOVICE hoppy VBA programmer.

I have several small VBA procedures that would be nice if they could be undone if the user clicked the button by mistake or changed thier minds after the fact.

Could you show me how to apply your code to the following procedure, after that I should be able to adapt that to my other VBA routines. Thanks in advance.

Sub DeleteActiveRow()

On Error GoTo TyingToDeleteAProtectedCell

    Dim ErrorResult As String
    Dim MyMessageBox As String

If Cells(Application.ActiveCell.Row, "Z").Value = "Don't Delete This Row" Then
         MyMessageBox = MsgBox("Wait!!!! You Can't Delete This Row...If you do, some of the Automated Functionality of this Workbook will be lost.", vbCritical, "123PAS Message: Functionality May be Lost!")
         Exit Sub
     End If

    Selection.Delete Shift:=xlUp
Exit Sub

    ErrorResult = MsgBox("Sorry, there are 'Protected Formulas' on this Worksheet." & vbCr & "Rows CANNOT be Deleted on this page." & vbCr & vbCr & "Please go to either the 'Section L', 'Section M', or 'Section C' Tabs and make your Deletions there. This page will be updated automatically.", vbCritical, "123PAS Error: Trying to Delete Protected Cells")
End Sub


Comment by: Jan Karel Pieterse (8-8-2012 13:15:28)

Hi Keith,

I'm afraid this code does not lend itself for the undo mechanism I proposed.

What I would do is have the delete code FIRST copy the row that is about to be deleted to some hidden undo worksheet. Then do the deleting. After that, Set Application.OnUndo to some code that can copy back the deleted row(s).


Comment by: Florian (26-2-2013 15:11:08)


How do you get the Undo Button reactivated after you used it once? When I use the button to execute my undo function I can do what ever i want but the Undo button stays gray.



Comment by: Jan Karel Pieterse (26-2-2013 15:35:04)

Hi Florian,

If you DL the example workbook, you'll see I designated a couple of shortcut keys to handle the undoes.

Key is, that from VBA you can only set one undo action to the User interface.

So your code would typically hold a collection of actions to undo. The last item of that collection would be handled by the undo command from the UI and the undo code you wrote would add the one-but-last item of the collection after processing that undo command.

Clear as mud?


Comment by: Florian (26-2-2013 15:58:26)

Hi Jan,

Thanks for the fast answer and yes it should work like you said, but after i use

Application.OnUndo "Undo Coloring", "UndoChange"

I can click on the nice little button in the upper left corner to do what i defined in "UndoChange".

I wrote Application.OnUndo "Undo Coloring", "UndoChange" at the end of the Sub "UndoChange" so that i could repeatedly execute that macro via the undo button.

It doesn't take it. The button stays gray after one use.

Only when i execute the "UndoChange" macro manually step by step (F8) will it reenable the undo button.

I couldn't find anything in the internet... only people with the same question but no answer anywhere.

Hope you can help me there?


Comment by: Jan Karel Pieterse (26-2-2013 16:37:13)

Hi Florian,

The workaround is rather simple.

Make sure you put the statement to schedule the next undo in a separate subroutine. From the scheduled undo sub, call the "ScheduleNext" routine using Application.Ontime.


Sub UndoStepwise()
    If mUndoClass Is Nothing Then Exit Sub
    If mUndoClass.UndoCount = 0 Then
        MsgBox "Last action undone"
        Set mUndoClass = Nothing
    End If
    Application.OnTime Now, "AddUndo"
End Sub

Sub AddUndo()
    Application.OnUndo "Undo Something", "UndoStepwise"
End Sub


Comment by: Florian (27-2-2013 08:30:57)

Hi Jan,

Many many many thanks! I would never come up with
application.ontime now

you saved me a lot of time and trouble :)



Comment by: Jan Karel Pieterse (27-2-2013 10:29:37)

Hi Florian,

An Excel dev needs tricks up his sleave to get things done :-)


Comment by: Milind (17-6-2013 11:16:29)

How do I' do this?
Please provide sample Excel file contains the above mentioned codes


Comment by: Jan Karel Pieterse (17-6-2013 11:28:01)

Hi Milind,

There is a sample file mentioned on this very page...


Comment by: Ivan Lanin (5-10-2013 19:08:58)

I just want to say thank you. I've managed to use your code to enable undo function for my VBA scripts.


Comment by: Mathan Kumar (13-8-2015 14:29:45)

Simply Awesome!


Comment by: Marvin (29-1-2016 20:13:50)

I just speak english a little.
I wish to know if you can help me?
I have this code

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Column = 3 And Target.Row > 2) Or (Target.Column = 4 And Target.Row > 2) Or (Target.Column = 5 And Target.Row > 2) Then
     Sheets("MADRE").Cells(Target.Row, 7).Value = Now
     End If
End Sub

And I want to add your routine for enable the "UNDO".

I send you a great greet.


Comment by: Jan Karel Pieterse (29-1-2016 20:58:32)

Hi Marvin,

You do understand that the undo method I demonstrate here can ONLY undo the change your code does on this line:

Sheets("MADRE").Cells(Target.Row, 7).Value = Now


Comment by: Marvin (29-1-2016 23:59:14)

Hi! Jan Karel!
Thank you for respond me.
I understand that the code only going to modifify
Sheets("MADRE").Cells(Target.Row, 7).Value = Now

But I guess, I do not expline me well.
I will try it.

I have 3 columns with 3 different prices, I have one 4th more (here I want to put the date when any of the above 3 were modified). But I will discover that if I have a big mistake I cannot do UNDO(CTRL+Z).

I want to know if it is possible add the UNDO at my funciton?
I hope I have explained. I am so sorry if i wrote bad some words or I did not explain me well.
Again, thank you very much.


Comment by: Jan Karel Pieterse (30-1-2016 13:47:13)

Hi Marvin,

In a normal module, paste this code:

Public gvPreviousValue As Variant
Public goModifiedCells As Range

Sub UndoTimeEdit()
    If not goModifiedCells Is Nothing Then
        goModifiedCells.Value = gvPreviousValue
        Set goModifiedCells = Nothing
    End If
End Sub

In your event routine:

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Column = 3 And Target.Row > 2) Or (Target.Column = 4 And Target.Row > 2) Or (Target.Column = 5 And Target.Row > 2) Then
        gvPreviousValue = Sheets("MADRE").Cells(Target.Row, 7).Value
        Set goModifiedCells = Sheets("MADRE").Cells(Target.Row, 7)
        Sheets("MADRE").Cells(Target.Row, 7).Value = Now
        Application.OnUndo "Undo time stamp", "UndoTimeEdit"
    End If
End Sub


Comment by: Marvin (31-1-2016 18:38:09)

Hai Jan Karel. I did things as you indicated me.
It was great! Thank you very much.
But I have a question more.

I noticed that I just can do one "undo" and not more.
Is there some way with that code that you give me to do n-quantity of "undo".

I hope not cause many problems. I am beginner in VBA.
Again Thank you very much.

By the way, It is the best webside about this topics that I've seen.


Comment by: Jan Karel Pieterse (1-2-2016 09:54:48)

Hi Marvin,

VBA can only schedule one Undo I'm afraid, but the routine you tell it to run can of course schedule the next undo for you. If you look at the example workbook that you can download, you'll see how I implemented to undo things step by step or all in one go.


Comment by: Samad Vaziri (5-2-2016 19:04:16)

Hi Jan,

I just enjoyed reading your awesome solution for undoing VBA changes. I am going to implement it into my current VBA application. Also I am thinking to add some bookmarks along with a comment in the undo stack to enable undoing a whole bunch of changes done in every interaction with the user. Perhaps I will need to use some hotkeys other than Ctrl+Z to manage undoing the whole changes done since I put the topmost bookmark in the stack.
I will appreciate your valuable comments about the idea.

Samad Vaziri        


Comment by: Jan Karel Pieterse (8-2-2016 13:53:30)

Hi Samad,

In the example I wrote up here I chose to have control+z undo all steps, but it makes just as much sense to have control+z undo only the last action your undo handler has recorded.


Comment by: William (7-3-2016 07:48:40)

Hi Jan,

First of all, thanks for the code it was an eye opener.

I have a question, say I have Rows(5).EntireRow.Delete in my code, would it be possible to undo the above change with your code? Thanks


Comment by: Jan Karel Pieterse (7-3-2016 09:19:23)

Hi William,

Yes in theory it would, but it would be quite a bit of work, as you would have to store the original content of the row prior to the deletion.


Comment by: Brian Murphy (7-12-2016 19:15:39)

Jan - Your site is like a goldmine!

I'd like to undo a cut/paste.

I use VBA to .Cut a cell range from one workbook to another, make a copy of the .Formula property for later use (b/c this nicely makes all cell references external), and .Cut the cell range back to where it came from (essentially undo the first .cut). This works, but has undesirable side effects on cell references in the source wbk that could be avoided if I could Undo the first cut instead of using a second cut.

Your VBA way of doing Undo doesn't seem to work with a Cut operation. Do you know of way to do this?


Comment by: Jan Karel Pieterse (8-12-2016 07:00:48)

Hi Brian,

So that makes you a gold digger, right? :-)
Seriously, Undoing a VBA-invoked cut is very difficult I think.


Comment by: Giang Pham (19-12-2016 09:47:14)

Hi Jan,
Your work is amazing.
Worked beautifully as I expected.
Howerver, when I tried to implement your method on Add-in it does not work. For example, I am building an Excel Add-in and I want the user to be able to Undo the functions of that Add-in.

Looking forward to your respond!

Thank you Jan!


Comment by: Jan Karel Pieterse (19-12-2016 10:17:50)

Hi Giang,

Without seeing any of your code this is going to be hard to solve :-)


Comment by: Giang Pham (19-12-2016 10:37:07)

' Highlight duplicate value
Sub highlight_dup(control As IRibbonControl)
    Application.ScreenUpdating = False
    Dim o As Range
    If mUndoClass Is Nothing Then
        Set mUndoClass = New clsExecAndUndo
        'Previous undoset, must be removed
        Set mUndoClass = Nothing
        Set mUndoClass = New clsExecAndUndo
    End If
    For Each o In Selection
        If Range(Selection.Item(1).Address & ":" & o.Address).Find(o.Value).Address <> o.Address Then
            With o.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 65535
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    Next o
    Application.OnUndo "Restore colours A1:A10", "Undo_highlight_dup"
    Application.ScreenUpdating = True
End Sub

Sub Undo_highlight_dup()
    If mUndoClass Is Nothing Then Exit Sub
    Set mUndoClass = Nothing
End Sub

These are the sub and the corresponding undo sub in .xlam file.
I already created 2 classes: clsExecAndUndo and clsUndoObject as you suggested


Comment by: Jan Karel Pieterse (19-12-2016 12:09:26)


For the undohandler to work you must use it also to make the changes that later need to be undone. See the example on the previous page of this article where I use "mUndoClass.AddAndProcessObject" to change a property and also add it to the undo stack.


Comment by: Giang Pham (20-12-2016 10:43:13)

Thank you for your suggestion, I will investigate further on your undohandler code :)


Comment by: Giang Pham (21-12-2016 04:04:30)

Hi Jan,
I was able to undo the highlight_dup thanks to your hint.
When investigating the mUndoClass.AddAndProcessObject, I notice this:
Function AddAndProcessObject(oObj As Object, sProperty As String, vValue As Variant)

Does this mean this Undo only works on changing Objects's property? What if I want to record the changes when I used methods of the object?

Thank you for your time.
You have been very helpful!


Comment by: Jan Karel Pieterse (21-12-2016 09:48:29)


What method are you considering to use? Indeed doing things like deleting a range of cells cannot be handled by this undo handler. That would require some serious programming as you would have to somehow store what the original (to be deleted) range of cells contained. Also, you would have to write all of the code to put everything back in place.


Comment by: Giang Pham (21-12-2016 10:20:27)

Yeah, i want to delete a range of cells. As you say, there is no easy way to undo that.
Thank you for your time!


Comment by: ZC (22-8-2017 15:15:40)


Would this work on the following code?
Sub Macro2()

Dim objCht As ChartObject
Dim ws As Worksheet

' loop through sheets in this workbook
For Each ws In ThisWorkbook.Worksheets
    ' loop through Chartobjects in sheet
    For Each objCht In ws.ChartObjects
        ' no need to select the chart or the series use With statement instead
        With objCht.Chart.SeriesCollection(1)
         .HasErrorBars = True
            With .ErrorBars.Format.Line
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorText1
                .ForeColor.TintAndShade = -0.0500000119
                .ForeColor.Brightness = 0
                .Transparency = 1
            End With
        End With
    Next objCht
Next ws

End Sub


Comment by: Jan Karel Pieterse (22-8-2017 16:08:00)

Hi Zara,

Theoretically, yes. See the previous page of this article for an example:


Comment by: warbe (17-7-2018 19:47:36)

I have undertaken the same effort by means of an undo class module which is not only very simple to use but also requires minimum code change.
BeginOfTransaction starts a new collection of changed cells
ChangeInTransaction provided with one or more cells about to be changed saves each cells properties (content, font, and interior)
EndOfTransaction prepares for undo (activates the undo icon)
BackoutTransaction sets all saved cells into their state prior the change. This procedure is invoked through the undo icon by a simple public procedure called OpUnDo.

I am about to publish this implementation here if you dont'mind


Comment by: Jan Karel Pieterse (18-7-2018 10:34:24)

Hi Warbe,

Sounds interesting. I expect this is a lot of code, given that there are a lot of properties to consider!


Comment by: Rich (9-2-2019 18:38:40)

Has there been any further info from Warbe ? on (7/17/2018 7:47:36 PM), he said "I am about to publish this implementation here if you don't' mind " but I don't see any more info on this.

Thanks for a GREAT page!


Comment by: Irian (11-3-2019 17:06:24)

Good afternoon,

thank you for all the hard work you have put into this. However, I can't get it to work.

In you implementation you write: 'If you go for (1), the OnUndo statement can just describe the set of actions done by this routine. Should you prefer to do (2), remember to change the text of the OnUndo statement accordingly.'. Could you emphasise what you mean by this as it is unclear to me what I have to edit in the OnUndo statement.

Also, I have other VBA code running, for instance to change the color of the selected row. Do I have to edit any of that code to work with the undo functionality?

I hope you can help me with this, as it would be a great functionality!

Kind regards,


Comment by: Jan Karel Pieterse (12-3-2019 09:59:44)

Hi Irian,

It means that if you want to enable the user to Undo each VBA change in the reverse order in which they were done, rather than all VBA actions in one step, you will have to update the Undo text after each undo so it states what the next undo would undo. this is done by using the Application.OnUndo statement as shown in the code, but using a different text as its first argument.


Comment by: JON FEENSTRA (18-7-2019 17:50:00)


What is the purpose of the following code in Class Modules(1)?

If mUndoObject Is Nothing Then
End If
If mvNewValue = "" Then
End If
If msProperty = "" Then
End If



Comment by: Jan Karel Pieterse (24-7-2019 13:28:00)

Hi Jon,

I'm afraid I've forgotten. I presume these were there as a placeholder to do checks if the correct information is present when exectuting the change, but I don't recall whether that is correct :-)


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here:

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.