OnRepeat and OnUndo Methods

An OnRepeat event handler runs when the user clicks Repeat on the Edit menu. A good use for an OnRepeat event handler is to allow the user to rerun a macro that performs a series of complicated operations on a worksheet. (Usually, clicking Repeat on the Edit menu after a macro has run will repeat only the last operation in the macro.)

You can use the procedure argument to the OnRepeat method of the Application object to customize the the Repeat menu item and to associate a procedure with the action of clicking this menu item.

For example, suppose that after pasting a date into a cell, you want to increment the date by one day in each successive paste from the cell above the active cell. To do this, you might use code such as that shown in the following procedure.

Sub TrapRepeat()
    Application.OnRepeat _ 
        text:= "Paste Next Day", _ 
        procedure := "PasteIncrement"
End Sub

Sub PasteIncrement()                            'OnRepeat handler.
    With ActiveCell
        .Value = .Offset(-1, 0) + 1            'Add a day.
    End With
End Sub

Similarly, an OnUndo event handler runs when the user clicks Undo on the Edit menu. You use the procedure argument to the OnUndo method of the Application object to customize the Undo menu item and to associate a procedure with the action of clicking this menu item.

For example, in a procedure that inserts a row containing data for a new record, the last action might be to enter data in a cell; when this happens, the Undo menu item changes to Undo Entry. However, choosing this command removes the information entered in the cell, when you'd probably want to delete the entire row with a command named Undo New Record instead.