+ Reply to Thread
Results 1 to 11 of 11

Custom Class to replace ThisWorkbook Events?

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Custom Class to replace ThisWorkbook Events?

    I have a number of involved projects that use several events in the ThisWorkbook object.

    I read recently that it would be possible to create a custom class and have these events processed there instead.

    I haven't started learning classes (my knowledge limits = I understand Get/Let Properties and use these in UserForms) so I was wondering if some kind forum user would provide a brief demo to get me started?

    For the demo I only require:

    1. whatever the code I need to insert into ThisWorkbook module to trigger/start the custom class event (not sure I have the right terminology/concept here!) and

    2. the code for the custom class module showing one example of a workbook event (e.g. BeforeSave or BeforeClose) converted to a class alternative. (Once I see 1 working example I can take it from there).
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Custom Class to replace ThisWorkbook Events?

    StackOverflow & Chip Pearson to the rescue again.

    http://stackoverflow.com/questions/9...-in-excel-2010

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

  3. #3
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Custom Class to replace ThisWorkbook Events?

    Spoke too soon. The examples given are for raising an Application class. How do I modify it so it is a class for ThisWorkbook?

    ThisWorkbook module
    Please Login or Register  to view this content.
    Class module ( ImAGoodListener )
    Please Login or Register  to view this content.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,462

    Re: Custom Class to replace ThisWorkbook Events?

    class1 module

    Please Login or Register  to view this content.
    Standard Thisworkbook module. Includes copy of custom code so you can see both events are fired.

    Please Login or Register  to view this content.
    Standard code module

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  5. #5
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Custom Class to replace ThisWorkbook Events?

    Thank you Mr Pope. + 1 I think I have grasped the concept.


    I only have one question that didn't occur to me before. What about Workbook_Open event?

    Since we are using Workbook_Open event procedure to trigger the creation of the class, if I create a class equivalent of Workbook_Open then that equivalent will never fire, correct?

  6. #6
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Custom Class to replace ThisWorkbook Events?

    You could put the enabling code in the Workbook_Open code for your Personal Macro Workbook.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  7. #7
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Custom Class to replace ThisWorkbook Events?

    Quote Originally Posted by mikerickson View Post
    You could put the enabling code in the Workbook_Open code for your Personal Macro Workbook.
    Yes I could but that's not what I'm wondering about.

    I'm OK with the class startup being in the Workbook_Open event. However I'm thinking would be impossible to create a working class equivalent of Workbook_Open (because the real Workbook_Open needs to kick in to start the class in the first place. As a result the class version of WB Open will never trigger, correct?)

  8. #8
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Unhappy Re: Custom Class to replace ThisWorkbook Events?

    OK I bite.

    Why doesn't the custom BeforeClose and Save events fire in my Custom Class experiment attached??
    Attached Files Attached Files

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,462

    Re: Custom Class to replace ThisWorkbook Events?

    Your BeforeClose signature was incorrect so it was simply a private routine within the class rather than an event of the MyTWB object.

    Try this modification for getting the class object Open event to fire. Although I don't know why you need to as the Workbook_Open_StartClasses routine is just a valid a place to do things, unless you are setting variables local to the class object. In which case you could simply expose a routine in the object and call it.

    Please Login or Register  to view this content.
    Class code
    Please Login or Register  to view this content.

  10. #10
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Custom Class to replace ThisWorkbook Events?

    It worked thanks! +1

  11. #11
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Wink Re: Custom Class to replace ThisWorkbook Events?

    Final version uploaded.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 08-30-2011, 02:23 AM
  2. [SOLVED] Re: Events, Class Modules and Addins ... oh my!
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-01-2006, 09:50 AM
  3. [SOLVED] Events, Class Modules and Addins ... oh my!
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2006, 09:23 PM
  4. [SOLVED] Class Events
    By Gareth in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 10-05-2005, 01:05 PM
  5. [SOLVED] Deactivating all events in ThisWorkbook
    By Gwen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-24-2005, 08:05 AM

Tags for this Thread

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