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!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
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.



Showing last 8 comments of 55 in total (Show All Comments):


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

' 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 (12/19/2016 12:09:26 PM)


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 (12/20/2016 10:43:13 AM)

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


Comment by: Giang Pham (12/21/2016 4:04:30 AM)

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 (12/21/2016 9:48:29 AM)


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 (12/21/2016 10:20:27 AM)

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 (8/22/2017 3:15:40 PM)


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 (8/22/2017 4:08:00 PM)

Hi Zara,

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


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:

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.