adding a worksheet with a pre-specified private sub.

[Brammer88]

Hi all,

I want to write a code that adds worksheets to my existing workbook. I know how to add sheets, but at the same time, I want the sheets that are created to contain a private sub that runs when I change a range of target cells on this newly created sheet later on. Does anyone know if this is possible? Thanks a million!

Bram

[Norie]

Why not use the workbook level SheetChange event?

That will be triggered by a change in any worksheet in the workbook.

[1nf1n1ty]

and you can exclude specified sheets with

Please Login or Register  to view this content.

[Brammer88]

thanx a lot! Did not know about this function.

[Brammer88]

Could you perhaps give a small example, because I cannot get it to work. I tried the following into the ThisWorkbook excel object. Thanks again for looking at it

Please Login or Register  to view this content.

[Norie]

The event is Workbook_SheetChange, to access it double click ThisWorkbook in the Project Explorer, select Workbook from the left dropdown and SheetChange from the right dropdown.

You'll now see this, which is where the code should go.

Please Login or Register  to view this content.

Sh is the worksheet that has been changed and Target is the range on that worksheet that has changed.

Now I'm not sure what the code you posted is meant to do but perhaps this will work.

Please Login or Register  to view this content.

[Brammer88]

Works perfectly. The code I putted in did not really matter, key was that I got the the workbook change function to work. Thanks again!!