Copy VB code to other workbook?

[john Dijkman]

Dear all,

I build the new workbooks from scratch and would like to
copy some VB code to the new workbook(s).

I am trying to copy the following code to the new workbook
("thisworkbook" module) as discussed and posted in "How to
write code to module" (03/04/03).

Private Sub Workbook_Activate()
Application.Run "TPBQ11.xls!CrMn"
End Sub

This is the sad attempt I made which does not work:

Sub test()
Application.VBE.MainWindow.Visible = False
Set TempModule = ActiveWorkbook.VBProject.VBComponents
("Thisworkbook")
strCode = "Private Sub Workbook_Activate()" & vbCr & _
"On Error Resume Next" & vbCr
& "Application.Run " & Chr(34) & "TPBQ11.xls!CrMn" & Chr
(34) & vbCr & _
"End Sub"
TempModule.CodeModule.AddFromString strCode
End Sub

I cannot find anything that explains how to do this and
cannot make the sample to wrk.

Any help would be really appreciated.

Kind reagards.

John

 

[Bob Kilmer]

Take a look here:
http://www.cpearson.com/excel/vbe.htm

 

[John]

Bob thanks for the email, very interesting.

I still get the following error messge:
Runtime error 1004
Programatic access to Visualbasic project is not trusted.

I have:
Security setting medium (excel 2002, OS:Win 2000) and a
personal dig. certificate.

Any idea why I still get this error message for the
following macro:

Sub test()
Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents
("Thisworkbook").CodeModule
StartLine = .CreateEventProc
("Activate", "workbook") + 1
.InsertLines StartLine, "msgbox ""hellome"",
vbOKonly"
End With
End Sub

Thanks again for all your help, really appreciated.

Kind regards,

John

 

[Tom Ogilvy]

http://support.microsoft.com/default.aspx?scid=kb;EN-US;282830
PRB: Programmatic Access to Office XP VBA Project Is Denied