Copy VB code to other workbook?

  • Thread starter Thread starter john Dijkman
  • Start date Start date
J

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