Creating An Undo Handler To Undo Changes Done By Excel VBA

Pages in this article

Conclusion

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.

Download

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

Feedback

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.

Comments

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

Comment by: Kurnia D Iskandar (10-12-2020 16:14:00) deeplink to this comment

Thank you this is awesome!

Comment by: Loet van Kimmenade (23-6-2021 12:04:00) deeplink to this comment

Kunnen ook afbeeldingen worden toegevoegd en ongedaan worden gemaakt?

Comment by: Jan Karel Pieterse (23-6-2021 13:56:00) deeplink to this comment

Hoi Loet,

Niet zoals de code nu geschreven is, maar ik denk wel dat het mogelijk is om er een aparte functie voor toe te voegen aan de klasses.

Comment by: Chris (28-3-2024 20:25:00) deeplink to this comment

How would I implement your code with this?:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ThisWorkbook.Names("HighlightRow")
.Name = "HighlightRow"
.RefersToR1C1 = "=" & ActiveCell.Row
End With
End Sub

Comment by: Jan Karel Pieterse (29-3-2024 10:30:00) deeplink to this comment

Hi Chris,

Depends; what exactly do you want to enable the user to Undo?

Comment by: Gary (2-10-2024 23:38:00) deeplink to this comment

Thank you!

Comment by: Vinicius Bubiak (3-10-2024 15:44:00) deeplink to this comment

Hey Jean, Im trying to implement your logic to undo macro painted cells and writting (using a button in a custom ribbon tab). Still working on the painting side. Facin some issues when implementing it:

After i run my painting macro i get this in instant window:

Object added to Undo stack, total objects:             3
Old value retrieved:         16777215
New value set:             14849448
ExecuteCommand succeeded
Undo count:                3
Undoing all actions, total count:         3
Undoing object             3             with old value:             16777215     Type:        clsUndoObject
Accessing property: Interior on object of type: Range

After this i get an erro box:
Runtime error 438
The object doesn't accept this property or method

when debuggin it point to --> mUndoChange.UndoAll when using Undochange and i get mUndoChange.Last when using UndoStepwise

Sub UndoChange(control As IRibbonControl)

    If mUndoClass Is Nothing Then
        Debug.Print "mUndoClass is Nothing. Cannot undo."
        MsgBox "No actions to undo. Please paint a cell first."
        Exit Sub
    End If
    Debug.Print "Undo count: ", mUndoClass.UndoCount
    If mUndoClass.UndoCount > 0 Then
        mUndoClass.UndoAll
        MsgBox "Actions undone."
    Else
        MsgBox "No actions to undo."
    End If
End Sub

Sub UndoStepwise(control As IRibbonControl)
    If mUndoClass Is Nothing Then Exit Sub
    mUndoClass.UndoLast
    If mUndoClass.UndoCount = 0 Then
        MsgBox "Last action undone"
        Set mUndoClass = Nothing
    End If
End Sub

Comment by: Jan Karel Pieterse (7-10-2024 10:39:00) deeplink to this comment

Hi Vinicius,

Apparently you have added some debug.print statements to the code. Perhaps you could send the file to me (see email address below) and outline the steps I need to take to reproduce your problem?

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: www.eileenslounge.com.