Events

An event is an action that is recognized by an object. In Microsoft Excel 5/95 VBA, there were few events available to the programmer. For instance, most controls responded to only a single event (the OnAction event) which allowed you to specify the procedure that would run when the user selected the control or in some cases typed a character in an edit box.

Microsoft Excel 97 VBA is a highly event-driven programming environment. Worksheets, Workbooks, UserForms, and Controls now respond to a wide variety of events. Placing VBA code inside the procedures triggered by various events allows your program to respond to those events.

In Microsoft Excel 5/95, for example, if you wanted to take some action when a workbook was saved by the user, you had to explicitly trap all of the methods by which the user could save the workbook. This included replacing the Save entry on the File menu, the Save toolbar button, and all the different keyboard shortcuts that could be used to save a file (Ctrl+S and Ctrl+F12 among others). This doesn't include trapping the Save As menu command and keyboard shortcuts, trapping custom toolbars and menus, or other keyboard remappings; it is very difficult to trap every possible save method.

In Microsoft Excel 97, however, the Workbook object has a BeforeSave event. Code placed within the procedure for this event will run automatically when the workbook is saved by any means, including programmatically, allowing you to respond to all workbook saves in a single location.

You may encounter situations in which you need to run code inside an event procedure conditionally, based upon whether the event was fired from the user interface or from some action taken by another procedure in your program. To accomplish this, you can add a new property to the object that owns the event in question, by adding a publicly declared variable to its module. Within a workbook's BeforeSave event, for instance, if you want to run code conditionally based upon whether the user is saving the workbook or your code is saving the workbook, you could do it in the following manner.

Public CodeSaved As Boolean

Private Sub Workbook_Open()
    CodeSaved = False
End Sub

Private Sub Workbook_BeforeSave(ByVal _
    SaveAsUI As Boolean, Cancel As Boolean)
    If CodeSaved Then
        MsgBox "Called from code."
        CodeSaved = False
    Else
        MsgBox "Called from UI."
    End If
End Sub

The declaration and event procedure shown above would go into the workbook's Object Module. When you save the workbook from another procedure in your program, you would first set the CodeSaved property of the workbook to True and then call the Save method of the workbook.

Sub SaveBook()
    wkbBook.CodeSaved = True
    wkbBook.Save
End Sub

If the SaveBook procedure will be used in a different project than the one that contains wkbBook, you must establish a reference from that project to the one containing wkbBook.

Working with Events

At the top of the code window of the Object Module for each of the objects that respond to events, there are two drop-downs. The left drop-down contains an object list, while the right one contains a list of event procedures for the selected object. To add code for an object's event procedure, simply choose that object from the left-hand drop-down and choose the event procedure from the right-hand drop-down. An event procedure, to which you can add your custom code, will be automatically generated by VBA. The following figure shows the Object module for a Worksheet with some of the available events in the drop-down.

Selecting an object in the VBE Project Window and clicking the View Code button at the top of this window will display the Object Module for the object. Object Modules for all objects other than UserForms can also be accessed by simply double-clicking that object in the VBE Project Window.

Double-clicking a UserForm in the VBE Project Window displays the UserForm itself. Double-clicking anywhere on a displayed UserForm brings up its Object Module and displays the default event procedure for the object that was double-clicked.

Many other objects respond to events in a similar fashion to that described above. Check the online Help for more details.

The AddinInstall and AddinUninstall Events

Microsoft Excel 97 has added two new events, AddinInstall and AddinUninstall, which are fired when the user adds or removes an add-in using the Tools/Addins dialog box. The AddinInstall event should be used to make any modifications to the user interface that the add-in requires. Conversely, the AddinUninstall should be used to remove any modifications to the Microsoft Excel environment made by the add-in.