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

StackOverflow & Chip Pearson to the rescue again.

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

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

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.

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.

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?

You could put the enabling code in the Workbook_Open code for your Personal Macro Workbook.

Originally Posted by mikerickson

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?)

OK I bite.

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

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.

It worked thanks! +1

Final version uploaded.