Add vba code into each individual sheet in workbook

[Boffa]

Hi VBA Pros, Hoping you can help,

I have a workbook that has many worksheets and each sheet when triggered will be emailed automatically to the the person who's name is the name of the worksheet. - This part is working fine. The challenge im struggling with is because the sheet requires data to be entered in by the receiving user, im wanting to give the user the functionality to double click on a row which will evoke the double click event. It will then display a form where they can enter their information via the form rather then scrolling back and fort between the columns.

I have no issues writing the code for the double click event (Which is using the used range and giving it a named range that matches the name thats on the worksheet) but I need to write this code on each individual sheet so that when the sheet is emailed to the user, the code for the double click event goes with it and the user can use this function.
There a re alot of sheets hence the preference to apply the code for the double click event on each sheet to be done automatically rather than manually one by one

All the worksheets are generated via vba and im hoping I can add a section of code that goes on each individual sheet

Is this at all possible ?

 

[Yongle]

For the future
Ensure your existing code is tailored to email sheet INCLUDING the VBA
- existing code may not require amending
Create a "model" sheet (with required VBA in its code window)
When creating new "Named" sheets copy that sheet (including VBA), using something like this (or doing it manually with Right-Click on Sheet Tab\ Move or Copy etc)

Sheets("Model").Copy Before:=Sheets(1)

Existing sheets
Would you like some code to a "convert" what is currently in your workbook to include the required VBA
The simplest way
- create "model" sheet (with required VBA in its code window)
The code would then
- loop relevant sheets in the workbook
- rename sheet Sheet Name as Sheet Name + XXX
- create a copy of "Model" and rename the copy Sheet Name
- copy the contents from Sheet Name + XXX to Sheet Name
- delete sheet Sheet Name + XXX

 

[Boffa]

Thanks Yongle never thought to do it this way - Simple now that ive done it and works a treat

Thanks for the help - Much Appreciated !

 

[John_w]

I have no issues writing the code for the double click event (Which is using the used range and giving it a named range that matches the name thats on the worksheet) but I need to write this code on each individual sheet so that when the sheet is emailed to the user, the code for the double click event goes with it and the user can use this function.
There a re alot of sheets hence the preference to apply the code for the double click event on each sheet to be done automatically rather than manually one by one

You could use the Workbook_SheetBeforeDoubleClick event (in the ThisWorkbook module) instead of Worksheet_BeforeDoubleClick and then there is only 1 code base rather than 1 for each sheet.