Inserting module to ActiveWorkbook

**owiec** · 03-11-2010, 07:03 PM

Hello,

How can I insert code in ThisWorkbook module in Workbook_Open event? I would like something like code in code so I can write in my macro line by line what should be putted in Workbook_Open in ActiveWorkbook.

**dominicb** · 03-11-2010, 07:12 PM

Good evening owiec

Try using this code :

Code:

Sub test()
Set wbcodemod = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.CodeName).CodeModule
Set codemod = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.CodeName).CodeModule
codemod.insertLines 1, "Public Sub Workbook_Open()"
codemod.insertLines 2, "'** Put your lines of code in here **"
codemod.insertLines 3, "End Sub"
End Sub

Before you run it set a reference within the VBE to Microsoft Forms 2.0.
Go to Tools > References, find and tck "Microsoft Forms 2.0 Object Library" and click OK.

HTH

DominicB

**owiec** · 03-12-2010, 03:35 AM

Hi,

Thanks for reply.

I get error Subscript out of range and it points to

Set wbcodemod = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.CodeName).CodeModule

When I put something like MsgBox ActiveWorkbook.CodeName its returne blank window. Should it be like that?

I know that ActiveWorkbook is working cuz when I put MsgBox ActiveWorkbook.Name it returns aproperiate value.

**dominicb** · 03-12-2010, 04:26 AM

Hi owiec

Your profile states you are running XL2003. Is this still the case?

I tested the code last night on an XL2003 and it worked fine. I've tested it again this morning on a different machine and it still works fine.

Try running the macro in the attached file and let me know how it goes.

HTH

DominicB