Microsoft Excel training courses in London

1
Event-Driven Macrosin Excel VBA
2
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.

3
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.

4
Event-driven macros

• The easiest event handlers to create are those
  which relate to workbooks and worksheets.

5
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.

6
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.

7
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.

8
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".

9
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.

10
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".

11
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.

12
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.

13
www.macresource.co.uk