Is it possible after creating a new sheet to insert code in it?

[Bobby]

Explanations:

1.I insert sheet

Sheets.Add
ActiveSheet.Name = demo


2.I would like to dynamically insert the following code in the newly created sheet:


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Call Module35.checkselection(Target)
End Sub

The purpose is to permit a user selection on specific cells.

Thank's in advance

 

[Ben McClave]

Hello,

You could try:

Sheets.Add
ActiveSheet.Name = "demo"

ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.AddFromString _
"Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)" & vbCr & _
" Call Module35.checkselection(Target)" & vbCr & _
"End Sub"

 

[Bobby]

Explanations:



1.I insert sheet



Sheets.Add

ActiveSheet.Name = demo





2.I would like to dynamically insert the following code in the newly created sheet:





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

Call Module35.checkselection(Target)

End Sub



The purpose is to permit a user selection on specific cells.



Thank's in advance

Ben it works! Brillant
Thank you!

 

[Bobby]

Explanations:



1.I insert sheet



Sheets.Add

ActiveSheet.Name = demo





2.I would like to dynamically insert the following code in the newly created sheet:





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

Call Module35.checkselection(Target)

End Sub



The purpose is to permit a user selection on specific cells.



Thank's in advance

 

[Bobby]

Explanations:



1.I insert sheet



Sheets.Add

ActiveSheet.Name = demo





2.I would like to dynamically insert the following code in the newly created sheet:





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

Call Module35.checkselection(Target)

End Sub



The purpose is to permit a user selection on specific cells.



Thank's in advance

Ben I get a subscript error on a new sheet???
Any idea?
Thank's

 

[Ben McClave]

Bobby,

I'm not sure. Did you check the box for "Trust Access to the VBA Project Module" in Excel's settings (2007: Excel Options>Trust Center>Trust Center Settings>Macro Settings)?

 

[Bobby]

Explanations:



1.I insert sheet



Sheets.Add

ActiveSheet.Name = demo





2.I would like to dynamically insert the following code in the newly created sheet:





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

Call Module35.checkselection(Target)

End Sub



The purpose is to permit a user selection on specific cells.



Thank's in advance

Yes I did and everything check is On. FYI this is Excel 2003.

 

[Ben McClave]

I can't seem to duplicate the error using the code I posted. Is this new sheet being created in a workbook other than the one containing this code? If so, then you would need to change the line "ThisWorkbook.VBProject.VBComponents..." to whatever the new workbook is called (or ActiveWorkbook). For example:

ActiveWorkbook.VBProject.VBComponents...

 

[Bobby]

Explanations:



1.I insert sheet



Sheets.Add

ActiveSheet.Name = demo





2.I would like to dynamically insert the following code in the newly created sheet:





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

Call Module35.checkselection(Target)

End Sub



The purpose is to permit a user selection on specific cells.



Thank's in advance

Hi Ben and thank you for you time. I found the reason for the situation. The sheet was not visible. After fixing that everything went fine!
Regards.

 

[Ben McClave]

Glad to hear that it worked out; and thanks for sharing the solution.