Strange behavior using Event Procedures In VBA RRS feed

  • Question

  • I'm getting strange behavior when trying to use Event Procedures In VBA.  Here's the setup.

    I have a .xls file that has an add-in .xla file.  I store all the code in the .xla file, since the code has a digital signature (to avoid the macros message), and the .xls file is a shared workbook which will be saved by users without access to the digital signature private key.

    On the .xla file, on the ThisWorkbook object, I include code to access the events of objects on the .xls file.  

    Public WithEvents App As Application
    Public WithEvents WBk As Workbook
    Public WithEvents WBs As Worksheet

    This seems to work fine...unless I code something in error.  This seems to "break" the code, and completely disable any of the functionality tied to the event procedures for Workbook and Worksheet.  I have to rollback my code (comment out the event procedures) for WBk and WBs.  If I enable the Workbook object, it still will not function unless I rename it, like so:

    Public WithEvents WBk1 As Workbook

    This seems a very illogical behavior to code to, so am I missing something in how to code event procedures, or is there some insight anyone has on this?

    Monday, September 28, 2009 11:26 PM


  • OK - I put this is a class module (as suggested in another forum) and the code works much better.
    • Marked as answer by Tim Li Monday, October 5, 2009 4:17 AM
    Wednesday, September 30, 2009 5:44 PM