RIchHW
06-13-2007, 09:48 AM
I've created and distributed a workbook to several users for them to keep track of information. I'm trying to make updating easier, since each user will add information to their copy of the workbook, so I've put all the macros in a hidden workbook that is put into the XLSTART folder for the user. When I make changes I just distribute a new hidden workbook that replaces the old one. The last thing I need to do (I think) is to remove the code in ThisWorkbook which runs at open and point it to run from the hidden workbook. I want to create a macro in the hidden workbook that will replace the code in ThisWorkbook module. I'll then have the users run the macro once and update their copy. I've found the following code but it does the updating in the hidden workbook instead of the one I want.
'Remove all code from ThisWorkbook code module
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 1, _
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.CountOfLines
' Insert the new code for ThisWorkBook
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("ThisWorkBook").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Sub Workbook_Open()" & Chr(13) & _
" Application.Run ""SI.xls!Open_Workbook"" " & Chr(13) & _
"End Sub"
End With
MsgBox "Done"
End Sub
Any ideas?
Edit Lucas: VBA tags added to code
'Remove all code from ThisWorkbook code module
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 1, _
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.CountOfLines
' Insert the new code for ThisWorkBook
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("ThisWorkBook").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Sub Workbook_Open()" & Chr(13) & _
" Application.Run ""SI.xls!Open_Workbook"" " & Chr(13) & _
"End Sub"
End With
MsgBox "Done"
End Sub
Any ideas?
Edit Lucas: VBA tags added to code