Creating Event-handler Macros In Microsoft Excel Vba

Many people's first taste of Excel macros is through the use of the recorder facility which generates a VBA macro that the user can then manually launch. Some people then graduate to editing recorded macros and perhaps writing their own code. As well as writing macros, Excel VBA also supports the creation of event-handlers which will execute when certain events take place in the Excel environment.

The simplest and quickest event handlers to create are those which relate to workbooks and worksheets. When you enter the Visual Basic Editor (VBE), the Project Explorer displays a series of icons representing each open workbook and, nested inside each, the worksheets they contain. These icons represent workbook and worksheet objects and Excel VBA allows you to enter special subroutines called event handlers directly inside them.

To create an event-handler, simply double-click the icon representing the worksheet or workbook in which you would like to place the event handler. This opens the code window at the top of which two drop-down menus are displayed, the one on the left labelled "General" and the one on the right "Declarations". Click on the drop-down on the left and choose "Workbook" or "Worksheet", depending on which object you are editing.

The drop-down money on the top left of the code window will then display all of the events available for event-handlers for that object. Choose an event from the list and Excel will automatically create and event-handler subroutine with the appropriate name. For example, if the object is a workbook and you choose the event "Activate", Excel will create a subroutine called "Workbook_Activate".

In addition to workbooks and worksheets, events can also be placed inside Excel UserForms and all of the objects they contain. To create a user form, simply choose UserForm from the Insert menu then use the Toolbox to add controls to the form. To create an event handler for any item you add to the form, simply double-click the object to open the code window for the form. Excel will then create the default event-handler for that object. If this is not the event-handler you wish to create, choose a different event from the drop-down menu in the top right of the code window.