Is there a means by which one can direct imported code into a sheet
module?

Thanks,
John

Hi John

See Chip's site
http://www.cpearson.com/excel/vbe.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


wrote in message ups.com...
Is there a means by which one can direct imported code into a sheet
module?

Thanks,
John

Chip's site is excellent, but it doesn't address how to import into a
sheet module. I am creating a sheet via VBA and I need to place code
in the sheet module. The amount of code needed precludes the use of
Chip's "Adding a Procedure to a Module" routine. Rather, I have the
code in a regular module with hopes of exporting and importing to the
new sheet module.

Thanks,
John

You can use this for a sheet module

Sub AddProceduretosheet()
Dim VBCodeMod As CodeModule
Dim LineNum As Long

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("sheet1").Code Module
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Sub MyNewProcedure()" & Chr(13) & _
" Msgbox ""Here is the new procedure"" " & Chr(13) & _
"End Sub"
End With
End Sub

Sub DeleteProcedurefromsheet()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("sheet1").Code Module
With VBCodeMod
StartLine = .ProcStartLine("MyNewProcedure", vbext_pk_Proc)
HowManyLines = .ProcCountLines("MyNewProcedure", vbext_pk_Proc)
.DeleteLines StartLine, HowManyLines
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


wrote in message oups.com...
Chip's site is excellent, but it doesn't address how to import into a
sheet module. I am creating a sheet via VBA and I need to place code
in the sheet module. The amount of code needed precludes the use of
Chip's "Adding a Procedure to a Module" routine. Rather, I have the
code in a regular module with hopes of exporting and importing to the
new sheet module.

Thanks,
John

Yes, that is the method from Chip's page I referenced. That method
seems a bit tedious, IMHO, for adding 389 lines of code to the sheet
module. Thus, I would like to import the code from another location.

Thanks,
John

Maybe : Import the code from a txt file

Sub AddcodefromTXTfile()
Dim VBComp As VBComponent
Application.VBE.ActiveVBProject.VBComponents.Item( "Sheet1") _
..CodeModule.AddFromFile ("C:\Code.txt")
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


wrote in message oups.com...
Yes, that is the method from Chip's page I referenced. That method
seems a bit tedious, IMHO, for adding 389 lines of code to the sheet
module. Thus, I would like to import the code from another location.

Thanks,
John