Insert macro automatically in new worksheet

foxhound1002

New Member
Joined
Oct 12, 2011
Messages
10
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?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
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

New Member
Joined
Oct 12, 2011
Messages
10
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
...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

New Member
Joined
Oct 12, 2011
Messages
10
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top