Implementing Undo

Unlike Excel's Undo feature, the undo technique used in the Text Tools utility is a single level. In other words, the user can undo only the most recent operation. Refer to the sidebar "Undoing a VBA Procedure" for additional information about using Undo with your applications.

The Text Tools utility saves the original data in a worksheet. If the user undoes the operation, that data is then copied back to the user's workbook.

In the Text Tools utility, recall that the Module1 VBA module declared two public variables for handling undo:

Public UndoRange As Range Public UserSelection As Range

Before modifying any data, the ApplyButton_Click procedure calls the SaveForUndo procedure. The procedure starts with three statements:

Set UserSelection = Selection

Set UndoRange = WorkRange


WorkRange, as you'll recall from the previous section, is a Range object that consists of the nonempty and nonformula cells in the user's selection. The third statement, above, erases any existing saved data from the worksheet. Next, the following loop is executed:

For Each RngArea In WorkRange.Areas ThisWorkbook.Sheets(1).Range _

(RngArea.Address).Formula = RngArea.Formula Next RngArea

This code loops through each area of the WorkRange and stores the data in the worksheet. (If the WorkRange consists of a contiguous range of cells, it will contain only one area.)

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 Edit menu will then contain a menu item: Undo Case Change. If the user selects the command, the UndoTextTools procedure, shown below, will be executed.

Private Sub UndoTextTools() ' Undoes the last operation Dim a As Range

On Error GoTo ErrHandler Application.ScreenUpdating = False With UserSelection

.Parent.Parent.Activate .Parent.Activate .Select 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 ErrHandler:

Application.ScreenUpdating = True MsgBox "Can't undo", vblnformation, APPNAME On Error GoTo 0 End Sub

The UndoTextTools procedure first ensures that the correct workbook and worksheet is 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).

Undoing a VBA Procedure

Computer users have become accustomed to the ability to undo an operation. Almost every operation that you perform in Excel can be undone. Even better, beginning with Excel 97, the program features multiple levels of undo.

If you program in VBA, you may have wondered whether it's possible to undo the effects of a procedure. Although the answer is yes, the qualified answer is it's not always easy.

Making the effects of your VBA procedures undoable isn't automatic. Your procedure needs to store the previous state so that it can be restored if the user chooses the Edit ^ Undo command. How you do this can vary depending on what the procedure does. In extreme cases, you might need to save an entire worksheet. If your procedure modifies a range, for example, you need to save only the contents of that range.

The Application object contains an OnUndo method, which lets the programmer specify text to appear on the Edit ^ Undo menu and a procedure to execute if the user chooses Edit ^ Undo. For example, the following statement causes the Undo menu item to display Undo my cool macro. If the user chooses Edit ^ Undo My Cool Macro, the UndoMyMacro procedure is executed:

Application.OnUndo "Undo my cool macro", "UndoMyMacro"

The companion CD-ROM contains a simpler example that demonstrates how to enable the Edit ^ Undo command after a VBA procedure is executed.

0 0

Post a comment