CODEWITHSUNDEEP
excelvba
Alex Sirbu
Alex Sirbu

Reputation: 37

VBA events for worksheets that are macro generated

I relatively new to VBA. Here is what I need to accomplish:

I need code to run when the value of some cells changes in some worksheets. I just learned how to do this using events. The problem I'm having is that the worksheets that need the event are not there to begin with, but are generated by a macro. Also, their number might vary depending on the data (i.e. sometimes the macro might generate 3, sometimes 10.. and will rename them using some cell values, so also the names differ every time).

From what I can see, the event that triggers the macro on cell change needs to be coded for the object itself (specific worksheets). Is there a way to do it for worksheets that are macro-generated, and don't exist in the workbook when first opened?

Any help would be much appreciated!

Upvotes: 0

Views: 127

Answers (1)

Gary's Student
Gary's Student

Reputation: 96791

A tiny trick:

The usual way to add a sheet is to use code like:

Sheets.Add

which will get you a fresh new sheet, but this new sheet will have no event code associated with it.

Instead create a template worksheet (called templ) and in this template worksheet include all necessary event macros. Now, instead of Adding a new worksheet, just:

Sheets("templ").Copy after:=Sheets(Sheets.Count)

you get a new worksheet, but with all macros attached!

Upvotes: 1

Related Questions