how to implement a NewWorkbook event?

[twd000]

I need some help creating some code that is executed when a new workbook is opened. I have read the VBA help and examples on the web, but they're not helping. The object/class module makes no sense to me.

Can someone please step me through, step by step, what code to write and where it should go. I assume it should be written in the ThisWorkbook module?

I have other code in ThisWorkbook (a Workbook_Activate event and some other stuff). Will a NewWorkbook event conflict with other code? does the code have to appear in a certain order?

here is some stuff I'd copied and pasted in various orders, with no luck:

Please Login or Register  to view this content.

[twd000]

here is the full code I'm trying to use:

Please Login or Register  to view this content.

[Leith Ross]

Hello twd000,

SubClassing is an advanced technique that allows you to redirect control or customize events. There are two parts to this approach. First is to create a Class or more simply a VBA object that responds to the same events as an existing VBA object. Secondly, this new object needs to be instantiated (created) so it will respond to the event before the original object does.

The code you posted is for the Class Module.

1) Insert a Class Module into your VBA Project
2) Copy the code into it
3) Change the Class name from Class1 to EventClassModule. This is the name VBA uses to create the object.
4) The class name appears in Name property of the Properties window. Press F4 to display the window.

Class Module Code

Please Login or Register  to view this content.

Creating the object is done in a Standard VBA module. This allows the new object to be accessible from any part of your project and remain in memory until the VBA project is terminated. Call or run InitApp when you are ready to have the events redirected.

Standard Module Code

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[twd000]

I'm still struggling with this

The code is never getting executed, even when a new workbook is opened

here is what I have in a Class Module called "EventClassModule":

Please Login or Register  to view this content.

and this is what I have in a Standard Module called Module 2:

Please Login or Register  to view this content.

nothing is happening.

do I need to paste any code into the object ThisWorkbook?

[Leith Ross]

Hello twd000,

Subclassing events is complex techinque. It would easier and faster for me to help you if you attach your workbook.

Sincerely,
Leith Ross

[twd000]

I don;t necessarily need to use subclassing

I'm looking for the easiest way to insert code that is activated when a new workbook is created

any way to make that happen is cool with me

here is the workbook

thanks for the help