Event-Driven Macros in Excel VBA

Event-driven macros Most people's first introduction to 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.

Event-driven macros 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.

Event-driven macros The easiest event handlers to create are those which relate to workbooks and worksheets.

The Visual Basic Editor 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.

The Visual Basic Editor These icons represent workbook and worksheet objects and Excel VBA allows you to enter special subroutines called event handlers directly inside them.

Creating an event-handler 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.

Creating and 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".

Creating an event-handler Click on the drop-down on the left and choose "Workbook" or "Worksheet", depending on which object you are editing. The drop-down on the left will then display all of the events available for event-handlers for that object.

Creating an event-handler 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".

User-form event-handlers In addition to workbooks and worksheets, event-handlers 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.

User-form event-handlers 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.

www.macresource.co.uk