Problem calling routine from a class in an add-in

Hi,

I've got three workbooks that end users will be using for data entry. The VBA code for the three workbooks is identical.

I'm trying to encapsulate the code into an add-in so I only have one copy of the code that I have to debug/maintain.

Furthermore, I've also encapsulated the code in the add-in into a class. The class isn't really a "usual" object modelling some data; rather, it's a utility class processing Worksheet_Change and Worksheet_Activate events. Hopefully this isn't a really bad idea, rather than keeping the code in standard modules in the add-in. Let me know if this is particularly bad design.

The application is called "Midas". Here are some code excerpts:

Midas.xlam:

Standard Module:

Please Login or Register  to view this content.

Class Module:

Please Login or Register  to view this content.

And in the end user workbook:

Please Login or Register  to view this content.

The Wbk_SheetChange event handler is triggering, but it's looking for the code in the end user workbook, instead of the class. Is there a way I can change

Please Login or Register  to view this content.

to call the private routine "Cases_Changed" (where "Cases" is the sheetname) residing in the class instead of looking for it in the workbook?

Thanks,
Scott

I'm happy to design this better. The code needs to do different things in different worksheets.

I think I could just do this, but it's klunky:

Please Login or Register  to view this content.

More code, apologies for the length:

Please Login or Register  to view this content.

P.S.: I'd prefer to keep the class methods private. And I'm open to someone saying using a class in this way is a bad idea. Although I'm not sure putting the code in standard modules in the add-in would fix this issue.