Excel

How to undo a procedure or macro

Almost every operation that you perform in Excel can be undone but Excel macros can not be undone after running them. It is also possible to undo the effects of a procedure or macro by using the Application.OnUndo method. All you need to store the previous state so that it can be restored if the user chooses the Undo command. For example, if a macro modifies a range you need to save the contents of that range and restore that range when the user chooses the Undo command.

OnUndo Event

Syntax: Application.OnUndo (Text, Procedure)

This method has two arguments:

  • Text
    Specify text to appear on the Undo menu
  • Procedure
    Procedure to execute when the user chooses Undo

The following example causes the Undo menu item to display Undo WeekDays Macro. If the user chooses Undo WeekDays Macro, the UndoWeekDays procedure is executed:

Example: Using Application.OnUndo to perform Undo operation for a subroutine

Option Explicit
Dim cellAddress As Range

Sub WeekDays()
 Set cellAddress = ActiveCell
 ActiveCell.Value = "Sunday"
 Selection.AutoFill Destination:=ActiveCell.Range("A1:A7"), Type:=xlFillDefault

 Application.OnUndo Text:="Undo WeekDays Macro", Procedure:="UndoWeekDays"
End Sub

Private Sub UndoWeekDays()
 MsgBox "Undo WeekDays Macro"
 cellAddress.Select
 ActiveCell.Range("A1:A7").Clear
End Sub
Video example: Undoing the macro

The WeekDays macro writes the weekdays and stores the ActiveCell reference in cellAddress variable which we use later for undo operation. The UndoWeekDays subroutine runs when the user perform Undo action.