Wednesday, February 11, 2009

Excel VBA Macro Tutorial: Excel's Event

An event handler procedure is a specially named procedure that's executed when a specific event occurs. Following are examples of types of events that Excel can recognize:
  • A workbook is opened or closed
  • A worksheet is activated or deactivated
  • An object is clicked
  • A worksheet is changed
  • A workbook is saved
  • A new workbook is created
  • A window is activaed or deactivated
  • A window is resized
  • A new worksheet is added

Excel's events can be classified as the following:
  • Workbook events. Events that occur for a particular workbook. Examples for this events are Open, Close, and BeforeSave.
  • Worksheet events. Events that occur for a particular worksheet. Examples include Change, and SelectionChange.
  • Chart events. Events that occur for a particular chart. Examples include Select.
  • Application events. Events that occur for a particular the application (Excel itself).
  • UserForm events. Events that occur for a particular UserForm or an object that contained on the UserForm. For example Click event.
  • Events not associated with objects. For examples OnTime and OnKey events.
There is a strict rule we must follow when naming event handler procedures, the name must be in the form of objectname_eventname. For example, the CommandButton control has the Click event, for a CommandButton whose name is cmdButton1, the event handler procedure must be named cmdButton1_Click.

Event-handling procedures should be placed in the correct location. If the procedure is placed in the wrong location, it does not respond to its event even though it is named properly. Here are some guidelines:
  • Event procedures for a user form (and its controls) should always go in the user
    form module itself.
  • Event procedures for a workbook, worksheet, or chart should always be placed in
    the project associated with the workbook.
  • If the object and the event can be found in the object and event list at the top of
    the editing window, it is all right to place the procedure in the current module.
  • Never place event procedures in a code module (those project modules listed under
    the Modules node in the Project window).

Following example will puts word "Excel VBA Macro" when worksheet activated:

Private Sub Worksheet_Activate()
ActiveSheet.Cells(1, 1).Value = "Excel VBA Macro"
End Sub

Related post: Excel VBA Macro Tutorial: Sub procedure

No comments:

Post a Comment