Creating An Undo Handler To Undo Changes Done By Excel VBA

Pages in this article

Implementation

The first step you need to take is copy the two class modules mentioned in the previous pages into your project. How to use these two class modules is described below.

Changing a property and adding it to the undo stack

Now that the class modules are in place it is time to put this to use in a normal project. The code snippet below shows how this is done (inside a normal module):

Option Explicit

Dim mUndoClass As clsExecAndUndo

Sub MakeAChange()
    Dim i As Integer
    If mUndoClass Is Nothing Then
        Set mUndoClass = New clsExecAndUndo
    Else
        'Previous undoset, must be removed
        Set mUndoClass = Nothing
        Set mUndoClass = New clsExecAndUndo
    End If
    For i = 1 To 10
        mUndoClass.AddAndProcessObject ActiveSheet.Cells(i, 1), _
                "Interior.Colorindex", 15
    Next
    Application.OnUndo "Restore colours A1:A10", "UndoChange"
End Sub

The subroutine above first checks to see whether an instance of mUndoClass already exists. If not, it instantiates a fresh one. If it does exist, there are two possibilities:

1. Clear the previous undo stack (as shown above)

2. Keep the previous set of actions to be undone later (remove everything between the Else....End If part of the sub above)

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.

As you may have noted, the syntax to use this is not exactly simple to implement, since having to enter the property as a string deprives you of intellisense. This is the price to be paid to get a versatile undo handler I guess.

If anyone comes up with a more convenient method I would be most happy to implement that into this code. Just write a comment below with your suggestions.

Un-doing what was done

The routine that is set to be called by Excel's Undo command is shown below. This one undoes all changes gathered in the mUndoClass module

Sub UndoChange()
    If mUndoClass Is Nothing Then Exit Sub
    mUndoClass.UndoAll
    Set mUndoClass = Nothing
End Sub

A second important routine I show here only undoes the last change made:

Sub UndoStepwise()
    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

It also detects when you have undone the last recorded action and if so, shows a message box and resets the mUndoClass variable.