Excel

Run a procedure when user chooses Repeat command

The Repeat command function is to repeat the last action you did in Excel. The keyboard shortcut for the Repeat command is Ctrl+Y, the same as the Redo command. When the Redo command has nothing left to redo, it changes functions and becomes the Repeat command. The quick access toolbar contains buttons that perform common actions: saving a document, undoing your last action, or repeating your last action.

If the Repeat button is not available on quick access toolbar, you can add it by following these steps:

  1. Click on the drop-down arrow in the right of the quick access toolbar.

  2. Click on the command you want to add, you can choose more commands if the command is not listed in the drop-down menu:

  3. The selected command will be added to the quick access toolbar.

OnRepeat

Syntax: Application.OnRepeat (Text, Procedure)

The OnRepeat method specifies the procedure that will run when the user chooses the Repeat command. This method has two arguments:

  • Text
    Specify text to appear on the Repeat menu (if any)
  • Procedure
    Procedure to execute when user chooses Repeat command

The Repeat command enables you to repeat the last action that you performed. For example, press Ctrl+Y (or click repeat button) immediately after running the WeekDays macro, it will repeat your action by running the macro again:

Option Explicit
Sub WeekDays()
 ActiveCell.Value = "Sunday"
 Selection.AutoFill Destination:=ActiveCell.Range("A1:A7"), Type:=xlFillDefault
 ActiveCell.Offset(, 1).Select
End Sub
Example: Repeating WeekDays macro

Using the Application.OnRepeat method, you can specify the procedure that will run when the user chooses the Repeat command. In the following example, the WeekDaysRepeat procedure will run when the user tries to repeat the WeakDays macro by clicking the Repeat button or by pressing the Ctrl+Y keyboard buttons:

Option Explicit

Sub WeekDays()
 ActiveCell.Value = "Sunday"
 Selection.AutoFill Destination:=ActiveCell.Range("A1:A7"), Type:=xlFillDefault
 ActiveCell.Offset(, 1).Select
 Application.OnRepeat Text:="WeekDays Macro", Procedure:="WeekDaysRepeat"
End Sub

Private Sub WeekDaysRepeat()
 MsgBox "You can not repeat WeekDays Macro"
End Sub
Application.OnRepeat runs the WeekDaysRepeat procedure when user tries the Repeat command on WeekDays macro.