Pages in this article
-
Class Modules (1)
-
Class Modules (2)
-
Implementation
-
Conclusion
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.