Copy VB code to other workbook?
 

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

Copy VB code to other workbook?
 

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

"john Dijkman" wrote in message
...
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

Copy VB code to other workbook?
 

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





-----Original Message-----
Take a look he
http://www.cpearson.com/excel/vbe.htm

"john Dijkman" wrote in message
...
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


.

Copy VB code to other workbook?
 

http://support.microsoft.com/default...b;EN-US;282830
PRB: Programmatic Access to Office XP VBA Project Is Denied

--
Regards,
Tom Ogilvy


John wrote in message
...
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





-----Original Message-----
Take a look he
http://www.cpearson.com/excel/vbe.htm

"john Dijkman" wrote in message
...
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


.