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

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

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

Hello,

You could try:

Sheets.Add
ActiveSheet.Name = "demo"

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

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

On Wednesday, November 7, 2012 12:09:13 PM UTC-5, Bobby wrote:
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!

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

On Wednesday, November 7, 2012 12:09:13 PM UTC-5, Bobby wrote:
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

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

On Wednesday, November 7, 2012 12:09:13 PM UTC-5, Bobby wrote:
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

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

Bobby,

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

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

On Wednesday, November 7, 2012 12:09:13 PM UTC-5, Bobby wrote:
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.

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

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...

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

On Wednesday, November 7, 2012 12:09:13 PM UTC-5, Bobby wrote:
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.

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

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