Insert macro automatically in new worksheet

[foxhound1002]

Is there a way to copy and paste a macro into an automatically created worksheet. I want to add an "event change" macro to every excel worksheet that is generated when a user presses a button. Is there any way to do that?

 

[Tom Urtis]

It's technically possible to do what you are asking, but two points:

(1)
It sounds like it will require that each user will need to have set the trusted access to the VBE on their local system, which you have no control over.

(2)
If the code is supposed to go into every new sheet module, why not simplify your project by sticking a Workbook level change event in the workbook module. Then, it will apply to every sheet (except those you specify in code to disregard) including sheets that get added later and you won't need to worry about what users' trusted access settings will be.

 

[foxhound1002]

Thank you so a workbook event change will be triggered every time a new workbook is created right? I'm assuming I can then make it so that if the workbook meets a name criteria whether it be pattern (e.g Demo workbook #) where the number increments based on how many workbooks the user creates. I appreciate the help and sorry for such a late reply.

 

[Tom Urtis]

...so a workbook event change will be triggered every time a new workbook is created right?

No, wrong. Creating a workbook will have no bearing on a Change event.

I'm assuming I can then make it so that if the workbook meets a name criteria whether it be pattern (e.g Demo workbook #) where the number increments based on how many workbooks the user creates.

That takes a left turn from your original question:

Is there a way to copy and paste a macro into an automatically created worksheet. I want to add an "event change" macro to every excel worksheet that is generated when a user presses a button. Is there any way to do that?

You kind of lost me on what it is you want to do. You started asking about placing code in modules of just-created sheets, then you moved to change events for creating workbooks. It could be me simply missing an obvious connection, but can you please repost what you are doing, what you want, and why, so someone can assist with those details in mind.

 

[foxhound1002]

Yeah sorry I have an excel sheet that has a button labeled "Create new customer" once the button is pressed it performs a series of actions and creates an excel sheet for the customer. The sheet is named based on the name input by the excel user. The sheet is created from running a macro so the user never creates the sheet per se but they do prompt the creation. The sheet is named based on the user name input (e.g 'input' sheet)

These macro generated excel sheets need to have a certain workbook change macro in them. I hope this explanation is thorough but I understand that what I know and what I say are two very different things. I appreciate the help.