+ Reply to Thread
Results 1 to 7 of 7

Event Procedures

  1. #1
    Registered User
    Join Date
    09-29-2009
    Location
    Denver
    MS-Off Ver
    Excel 2003
    Posts
    4

    Question Event Procedures

    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.
    Please Login or Register  to view this content.
    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:
    Please Login or Register  to view this content.
    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?

    Now I've got the Workbook object recognized if I have the Worksheet declaration commented out, but no matter how I tinker with the Worksheet object and change the name, whenever I uncomment the declaration, none of the code in the .xla runs when the .xls is opened.

    Thanks for any help!
    Last edited by Leith Ross; 09-29-2009 at 08:09 PM. Reason: Added Code Tags

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    re: Event Procedures

    Hello pflipper,

    Welcome to the Forum!

    You do know that these declarations need to be inside a Class module and not a standard VBA module. These will only catch events for newly created objects and not pre-existing ones like the other workbook.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    09-29-2009
    Location
    Denver
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Event Procedures

    Hi Leith,

    Thanks for your input. I transferred my withevents declarations and associated code to a class module, and the code seems to be functioning again. Thanks!

    Maybe there is some misinformation out there - I was under the impression after reading the following site:

    http://www.cpearson.com/excel/Events.aspx

    that the application event, as well as other workbook and worksheet events, could be under the ThisWorkbook object or a class object, but wasn't required that I do either. Am I reading this article incorrectly, or does the article have some misinformation?

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Event Procedures

    I think Chip is pretty clear:
    OBJECT MODULES -- Everything related to event handling -- the definition of event procedures, creating a WithEvents variable to receive events, and creating your own events -- takes place within Object Modules. Besides setting the EnableEvents property, there is nothing related to events in a standard code module; every thing is in an object module. An Object Module is any one of the following:

    A Class module.
    A Sheet module (either a worksheet or a chart sheet).
    The ThisWorkbook module
    The code module behind a User Form
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    09-29-2009
    Location
    Denver
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Event Procedures

    Hi shg,

    OK - that is clear, and I did read that on the site. As I mentioned in my first post, I did at first use the ThisWorkbook object as a place to store my WithEvents variables and all associated code. Does that not fit the text that you just quoted?

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Event Procedures

    That will handle workbook-level events for the workbook it's in.

    If you want to handle workbook events for all workbooks (e.g., from Personal.xls), it would go in a class module.

  7. #7
    Registered User
    Join Date
    09-29-2009
    Location
    Denver
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Event Procedures

    OK - thanks for the clarification.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1