Introduction to Event Procedures

A special type of VBA procedure, an event procedure, is used to react to specific events. This procedure contains VBA code that handles a particular event. Some events may require a single line of code, while others can be more complex. Event procedures have names, which are created in the following way:


In the parentheses after the name of the event, you can place parameters that need to be sent to the procedure. The programmer cannot change the name of the event procedure.

Before you can write an event procedure to react to an Excel event, you need to know:

■ The name of the particular object and event that you want to respond to Objects that can respond to events display a list of events in the Procedure drop-down list in the Code window (Figure 14-1). Also, you can use the Object Browser to find out the names of the events (Figure 14-2).

■ The place where you should put the code

Some events are coded in a standard module; others are stored in a class module. While workbook, chart sheet, and worksheet events are available for any open sheet or workbook, to create event procedures for an embedded chart, query table, or Application object, you must first create a new object using the With Events keyword in the class module.

Figure 14-1:

You can find out the event names in the Code window.

Figure 14-1:

You can find out the event names in the Code window.

-¿r Object Browser

. |D| xj


n «1 1 a 1 ti

1 Worksheet


Members of Worksheet'

©i Windows

f Activate j^J

0 Workbook

9 BefoteDoubleCIIck

CS Workbooks

9 BeforeRlcthtCNck

¡HI Works he et

f Calculate

^ WorksheetFunction


9 Change

g® Worksheets

9 Deactivate

dp Constants

9 FollowHyperllnk


9 PIvolTableUcdate

lijp XIApplyNamesOrder

9 SelectloriChange 1

i=P XIArabicModes


Class Worksheet Member of Excel

Class Worksheet Member of Excel

Figure 14-2:

You can find out the event names in the Object Browser.

0 0

Post a comment