Microsoft Office Tutorials and References
In Depth Information
What Makes a Good Utility?
After the specified operation is performed, the code then uses the OnUndo method to specify the
procedure to execute if the user chooses Undo. For example, after performing a case change
operation, this statement is executed:
Application.OnUndo “Undo Change Case”, “UndoTextTools”
Excel’s Undo drop-down list will then contain a menu item: Undo Change Case (see Figure 16-6).
If the user selects the command, the UndoTextTools procedure, shown next, will be executed.
Figure 16-6: The Text Tools utility includes a single level of undo.
Private Sub UndoTextTools()
‘ Undoes the last operation
Dim a As Range
On Error GoTo ErrHandler
Application.ScreenUpdating = False
With UserSelection
End With
For Each a In UndoRange.Areas
a.Formula = ThisWorkbook.Sheets(1).Range(a.Address).Formula
Next a
Application.ScreenUpdating = True
On Error GoTo 0
Exit Sub
Application.ScreenUpdating = True
MsgBox “Can’t undo”, vbInformation, APPNAME
On Error GoTo 0
End Sub
The UndoTextTools procedure first ensures that the correct workbook and worksheet are
activated and then selects the original range selected by the user. Then it loops through each area of
the stored data (which is available because of the UndoRange public variable) and puts the data
back to its original location (overwriting the changes, of course).
Search JabSto ::

Custom Search