vba - Add Module code of BeforeDoubleClick_event to dynamically created worksheets

Question

I have this code:

For a = 1 To 5
    strFoglio = "SheetName" & a
    Sheets.Add

    ActiveSheet.Name = strFoglio

    ActiveSheet.Move after:=Sheets(Sheets.Count)
Next a

Is there a way to write code on these brand new sheets for example:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
    Dim myRange As Range
End sub

Naturally, I'd like to do directly in the For...Next loop and not manually.

Answer 1

The code below will run your For loop, create 5 sheets, and per sheet will call a Sub CodeCopy which will copy the code lines from a Module (in this example the code in "Sheet1") into the new created sheet.

Code

Option Explicit

Sub CreateSheets()

Dim a As Long

For a = 1 To 5
    Sheets.Add
    ActiveSheet.Name = "SheetName" & a
    ActiveSheet.Move after:=Sheets(Sheets.Count)
    Call CodeCopy(ActiveSheet.Name)
Next a

End Sub

' **********

Sub CodeCopy(DestShtStr As String)    

' Macro to copy the macro module from sheet1 to a new Sheet 
' Name of new sheet is passed to the Sub as a String
' Must install "Microsoft Visual Basic for Applications Extensibility library" 
' from Tools > References.

Dim i           As Integer
Dim SrcCmod     As VBIDE.CodeModule
Dim DstCmod     As VBIDE.CodeModule

' set source code module to code inside "Sheet1"
Set SrcCmod = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.Worksheets("Sheet1").CodeName).CodeModule
Set DstCmod = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.Worksheets(DestShtStr).CodeName).CodeModule

' copies all code line inside "Sheet1"
' can be modified to a constant number of code lines
For i = 1 To SrcCmod.CountOfLines
   DstCmod.InsertLines i, SrcCmod.Lines(i, 1)
Next i

End Sub

Code in "Sheet1" that will be copied to all new created sheets is:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim myRange As Range

End Sub

---

Instructions

In order for this code to work, you need to allow the following 2 things:

1. Go to Tools >> References, and add a reference to "Microsoft Visual Basic for Applications Extensibility" library (screen-shot below)

2. In Excel Main menu, go to Developer Menu, then select Macro Security, the click V to allow "Trust access to the VBA project object model" (screen-shot below)