Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy an image (or picture) from one workbook to a new sheetin another workbook | Excel Worksheet Functions | |||
Excel-how to link source workbook to copy of destination workbook | Excel Worksheet Functions | |||
Help...Code to copy sheet from closed workbook | Excel Discussion (Misc queries) | |||
Copy empty cells from one workbook to another - Code | Excel Discussion (Misc queries) | |||
copy and paste using code from workbook to workbook | Excel Discussion (Misc queries) |