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

  • Thread starter Thread starter Bobby
  • Start date Start date
B

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
 
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"
 
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
Click to expand...

Ben it works! Brillant
Thank you!
 
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
Click to expand...
 
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
Click to expand...

Ben I get a subscript error on a new sheet???
Any idea?
Thank's
 
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)?
 
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
Click to expand...

Yes I did and everything check is On. FYI this is Excel 2003.
 
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...
 
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
Click to expand...

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