Creating code in new worksheets

[DrBowes]

I have a workbook containing several sheets, all of which have the same Worksheet_SelectionChange event. (The events only run when certain columns and rows are selected.)

I want this event to also apply on new sheets added by the user by pressing a button. This sheet adding macro, as well as pasting formats from an existing sheet will also paste the sheet's code.

I think the way this could be done would be by pasting VBproject.VBComponents.... into a text file and re-importing into the new sheet module's code. However I could do with some more help.

My next question is, can this still work if I have password protected my VBA project?

(I guess a work around could be unhiding existing hidden sheet that already have the code but I would like to know the proper way to do this.)

Thanks very much

Dr B

 

[Loomah]

Hi Doc
As an alternative why not use the Workbook_SheetSelectionChange event? If your code is the same for all sheets and applies to all sheets this is the way to go. When youy add new sheets there's then no need to copy code. This also means you only have one procedure to maintain.

Happy Daze
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[DrBowes]

Thanks very much, I should have thought of this!

Can run different code on different sheets using
If sh.name = "Sheet1" Then .... etc with this also.