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

I inserted a Sheet1 and created c:\code.txt and the sub worked great.
My sincere thanks to you....I'm almost there.

But when I change the item name to the sheet I am using ("Activities")
I get a "Subscript out of range" error. The sheet exists, is not
protected, and has an empty module. I think that error typically means
that the referenced sheet does not exist.

Any ideas?

Thanks,
John

Hi John

It use the code name and not the sheet name on the tab
If you are in the VBA editor and select a sheet and press F4 you can change the code name

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


wrote in message oups.com...
I inserted a Sheet1 and created c:\code.txt and the sub worked great.
My sincere thanks to you....I'm almost there.

But when I change the item name to the sheet I am using ("Activities")
I get a "Subscript out of range" error. The sheet exists, is not
protected, and has an empty module. I think that error typically means
that the referenced sheet does not exist.

Any ideas?

Thanks,
John

Ok. Can I set the code name when I create the sheet?

I think it is easier to do this for you

1) open a new workbook
2) delete all sheets except one
3) Add all the code you want in this sheet
4) FileSaveAs choose Template in Save as Type and save the file

Then with code you can insert the sheet with the code in one step
Sheets.Add Type:="C:\templatetest.xlt"






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


wrote in message oups.com...
Ok. Can I set the code name when I create the sheet?

If you save it the template folder then you can use this

Sheets.Add Type:=Application.TemplatesPath & "\templatetest.xlt"

Also easy to insert it manual then (right click on a sheet tab and choose insert)


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


"Ron de Bruin" wrote in message ...
I think it is easier to do this for you

1) open a new workbook
2) delete all sheets except one
3) Add all the code you want in this sheet
4) FileSaveAs choose Template in Save as Type and save the file

Then with code you can insert the sheet with the code in one step
Sheets.Add Type:="C:\templatetest.xlt"






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


wrote in message oups.com...
Ok. Can I set the code name when I create the sheet?