Application Events

< Day Day Up >

The events for the Application occur when any workbook is created, opened, or changed. If you need to write an event procedure at the Application level, you need to create a new object in a class module. After the new class module is created, you can attach macros with a variety of events, such as NewWorkbook, SheetActivate, or WorkbookOpen.

Table 12-3 describes the commonly used Application events that can be programmed in your workbooks.

Table 12-3: Commonly Monitored Events by the *Application* Object
Event	Action That Triggers the Event
NewWorkbook	Monitors the NewWorkbook event for the Excel application. The event is triggered when a new workbook is created.
SheetActivate	Monitors the SheetActivate event for the entire Excel program. The event is triggered when any sheet is activated within the program.
WorkbookOpen	Monitors the WorkbookOpen event for the Excel application. The event is triggered when any workbook is opened within the Excel window.

Inside Out
Locate Events Using the Object Browser

The Object Browser is a useful tool that can help you learn about objects, their properties, and their methods. The Object Browser is also useful when trying to find which objects are supported by a particular event. For example, say you would like to find out which objects support the Change event. Activate the Visual Basic Editor, and press F2 to display the Object Browser window. Make sure <All Libraries> is selected, and then type Change and click the binoculars icon, as shown in the following graphic.

click to expand

The Object Browser displays a list of matching items. Events are indicated with a small yellow lightning bolt. From this list, you can see which objects support the Change event.

Notice how the list is divided into three columns: Library, Class, and Member. The match for the item you are searching for might appear in any of these columns. The name of an event or term belonging to one library or class might be the same as that for another belonging to a different library or class, although they probably don’t share the same functionality. When clicking each item in the Object Browser list, check the status bar at the bottom of the list for the syntax. You might find that one class or library treats an event differently than another.

Turning on Application Event Monitoring

In the previous sections, we discussed how to create Worksheet events and Workbook events. Those events are for a particular workbook. If you need to monitor events for the entire Excel application, use Application-level events. To use Application events, you must enable event monitoring. Follow these steps to activate Application event monitoring:

Create a new class module.
Set a name for the class module in the Properties window under Name. For example: AppEventClass.
In the class module, declare a public Application object using the WithEvents keyword. For example:
```
Public WithEvents Appl As Application
```

To test the application event procedure, enter the following examples in the new class module after the public variable:

Private Sub Appl_NewWorkbook(ByVal Wb As Workbook)
    MsgBox "You created a new workbook."
End Sub
 
Private Sub Appl_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    MsgBox "You closed the workbook."
End Sub
 
Private Sub Appl_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
    MsgBox "You are printing the workbook."
End Sub
 
Private Sub Appl_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal _
    SaveAsUI As Boolean, Cancel As Boolean)
    MsgBox "You saved your workbook."
End Sub
 
Private Sub Appl_WorkbookOpen(ByVal Wb As Workbook)
    MsgBox "You opened a workbook."
End Sub

Create a variable that you can use to refer to the declared Application object in the class module. This should be a module-level object variable, declared in a regular VBA module or in the ThisWorkbook object. For example:
```
Dim ApplicationClass As New AppEventClass
```
Connect the declared object with the Application object. This is often done in a Workbook_Open procedure. For example:
```
Private Sub Workbook_Open()
Set ApplicationClass.Appl = Application
End Sub
```

After you save the workbook, close it, and reopen it to trigger the Workbook_Open event procedure, the events attached to the Application object will be activated.

Understanding how the events are triggered, as well as the sequence in which they are executed, is crucial when designing your Excel applications. Use the preceding example to play with your application and test the firing sequence. Taking time and effort in the planning stages of your event sequences will save a lot of time and frustration in the development of your current and future projects.

Detecting When a Workbook Is Opened

Because Excel only allows one copy of a workbook to be open, it’s important to determine if the workbook is currently open or needs to be opened. If you do not verify the status of the file, you will receive an error and your event will stop.

For example, The Garden Company uses a file named Invoice.xls that’s dependent on the ProductList.xls file. The Invoice.xls file uses a lookup to input the correct product name on the invoice. It’s crucial for the ProductList.xls file to be open for the Invoice.xls file to operate properly. To avoid lookup errors, the following function, named WorkbookOpen, was created to test whether the ProductList.xls file is currently open. The function will return the answer True if the workbook is open.

Function WorkbookOpen(WorkBookName As String) As Boolean
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.WorkBooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

Once the function has been added to the ThisWorkbook object within the Invoice.xls file, you can use the function to evaluate whether the desired workbook is open. The following If...Then...Else statement, which you could use as a subroutine within a larger procedure, ensures the ProductList.xls workbook will be open when you need to use it in a procedure.

If Not WorkbookOpen("ProductList.xls") Then 
    Workbooks.Open "ProductList.xls"
End If

This chapter has exposed you to three layers of events: application, workbook, and worksheet. When analyzing the needs of your workbook, it’s important to remember your final goals for your project. When you have a list of event procedures that are required, the next step is to determine at which level the procedures should be stored. Remember that the most common error while programming event procedures is coding the events for the wrong object.

Keep in mind as you are programming the required event procedures that some triggers actually cause several events to run. The best way to get a feel for event procedures is to thoroughly test them. You might go through several drafts before you finalize how you want to set up your event handlers.

< Day Day Up >