XL: How to Create Application-Level Event Handlers (213566)

MORE INFORMATION

• Declare a variable for the Application object using the WithEvents keyword. The WithEvents keyword can be used to create an object variable that responds to events triggered by an ActiveX object (such as the Application object).NOTE: WithEvents is valid only in a class module.
  
  
• Create the procedure for the specific Application event. For example, you can create a procedure for the WindowResize, WorkbookOpen, or SheetActivate event of the object you declared using WithEvents.
• Create and run a procedure that starts the event handler.

Creating and Initiating the Event Handler

1. Open a new workbook.
2. Start the Visual Basic Editor.
3. Click Class Module on the Insert menu. This will insert a module titled "<book name> - Class1 (Code)" into your project.
   
   
4. Enter the following line of code in the Class1 (Code) module:

   Public WithEvents appevent As Application

   The WithEvents keyword makes the appevent variable available in the Object drop-down in the Class1 (Code) module window.
5. In the Class1 (Code) module window, click the Object drop-down and then click appevent in the list.
6. In the Class1 (Code) module window, click the Procedure drop-down and then click WindowResize in the list. This will add the following to the Class1 (Code) module sheet:

      Private Sub appevent_WindowResize(ByVal Wb As Excel.Workbook, _
          ByVal Wn As Excel.Window)
   
      End Sub
   					

7. Add code to the Class1 (Code) module sheet so that it appears as follows:

          Public WithEvents appevent As Application
   
          Private Sub appevent_WindowResize(ByVal Wb As Excel.Workbook, _
              ByVal Wn As Excel.Window)
   
              MsgBox "you resized a window"
   
          End Sub
   					

   Next, you have to create an instance of the class and then set the appevent object of the instance of the Class1 to Application. This happens because when you declare a variable, WithEvents, at design time, there is no object associated with it. A WithEvents variable is just like any other object variable - you have to create an object and assign a reference to the object to the WithEvents variable.
   
   
8. On the Insert menu click Module to insert a general type module sheet into your project.
9. In this module sheet, enter the following code:

         Dim myobject As New Class1
   
         Sub Test()
             Set myobject.appevent = Application
         End Sub
   					

10. Run the test macro.
    
    You have just set the event handler to run each time you resize a workbook window in Microsoft Excel.
11. On the File menu, click Close and Return to Microsoft Excel.
12. Resize a workbook window. A message box with "you resized a window" will be displayed.

How to Turn Off the Event Handler

1. Start the Visual Basic Editor.
2. In the macro code you entered in Step 9, change the macro to:

         Sub test()
             Set myobject.appevent = Nothing
         End Sub
   					

3. Run the test macro again.
4. On the File menu, click Close and Return to Microsoft Excel.
5. Resize a workbook window.
   
   The message box will not display.