XL97: How to Create Application Level Event Handlers

 Last reviewed: March 13, 1998
Article ID: Q158244
The information in this article applies to:
  • Microsoft Excel 97 for Windows

SUMMARY

If you want a particular event handler to run whenever a certain event is triggered, you can write an event handler for the Application object. Event handlers for the Application object are global, which means that as long as Microsoft Excel is open, the event handler will run when the appropriate event occurs, regardless of which workbook is active when the event occurs.

This article describes how to create an Application level event handler and provides an example.

MORE INFORMATION

To create an Application-level event handler, you must use the following basic steps:

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

The following example uses these steps to set up a global event handler that displays a message box whenever you resize any workbook window (the event firing 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.

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

  1. In the Class1 (Code) module window, click the Object drop-down and then click appevent in the list.

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

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

    1. On the Insert menu click Module to insert a general type module sheet into your project.

    2. In this module sheet, enter the following code:

      Dim myobject As New Class1 

            Sub Test()
          Set myobject.appevent = Application
      End Sub

    3. Run the "test" macro.

    You have just set the event handler to run each time you resize a workbook window in Microsoft Excel.

    1. On the File menu, click "Close and Return to Microsoft Excel".

    2. Resize a workbook window.

    A message box with "you resized a window" will be displayed.

    How to turn off the event handler

    If you close the workbook that contains the above project, the application level event handler will be turned off. To programmatically turn off the event handler, do the following:

    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.

    REFERENCES

    For more information about Class Modules, in the Visual Basic Editor click "Contents and Index" on the Help menu, click the Index tab, type the following text 

       class module
    and then double-click the selected text to go to the "Module and Class Module Commands (Insert Menu)" topic.

    • Additional query words: vbe 8.00 XL97
    Keywords : kbcode kbprg xlvbahowto xlvbainfo kbfaq
    Version : WINDOWS:97
    Platform : WINDOWS
    Issue type : kbhowto

    THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

    Last reviewed: March 13, 1998
    © 1998 Microsoft Corporation. All rights reserved. Terms of Use.