Creating OnEvent Procedures

Certain objects in Visual Basic have properties and methods that are associated with specific events. For example, the Button object has an OnAction property, which is associated with clicking the button; and the Application object has an OnRepeat method, which is associated with clicking Repeat on the Edit menu. These events are generated by Microsoft Excel, sometimes as the result of user input. An example of an event that isn't the result of user input is the arrival of data from another application by means of OLE (see the OnData property).

If you associate a procedure with one of these properties or methods, that procedure — called an OnEvent procedure or an event handler — will run whenever the event associated with the property or method occurs.

The following table lists the properties and methods you can use to trap events.

Property or method	Event that causes the associated procedure to run

OnAction	Clicking a control or graphic object, clicking a menu command, or clicking a toolbar button
OnCalculate	Recalculating a worksheet
OnData	The arrival of data from another application by way of DDE or OLE
OnDoubleClick	Double-clicking anywhere on a chart sheet, dialog sheet, module, or worksheet
OnEntry	Entering data using the formula bar or editing data in a cell
OnKey	Pressing a particular key or key combination
OnRepeat	Clicking Repeat on the Edit menu
OnSheetActivate	Activating a chart sheet, dialog sheet, module, worksheet, workbook, or Microsoft Excel itself
OnSheetDeactivate	Deactivating a chart sheet, dialog sheet, module, worksheet, workbook, or Microsoft Excel itself
OnTime	Waiting until a specific time arrives, or waiting for a specified time delay
OnUndo	Clicking Undo on the Edit menu
OnWindow	Activating a window

There are several steps involved in creating and using any type of OnEvent procedure:

You must create the procedure (the event handler) you want to run when the specified event occurs.
Elsewhere in your Visual Basic code, you must associate the event handler with the event you want to respond to. After the statement that associates the event with its handler is executed, the handler runs whenever the event occurs; this is called trapping the event.
When you want to stop trapping this event, you must disassociate the event from the event handler.

To associate an event with an OnEvent procedure

Set the property associated with the event — or set the procedure argument of the method associated with the event — to the name of the OnEvent procedure, as shown in the following examples.

ActiveSheet.Buttons("MyButton").OnAction = "ButtonClickHandler"
Application.OnRepeat text:= "Paste Again", procedure:= "PasteAgain"

To disassociate an event from an OnEvent procedure

Set the property associated with the event — or set the procedure argument of the method associated with the event — to the empty string (""), as shown in the following examples.

ActiveSheet.Buttons("MyButton").OnAction = ""
Application.OnRepeat text:= "Paste Again", procedure:= ""

The following sections show how event trapping is done with specific types of events.