Copy open event to active workbook

[RIchHW]

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

[lucas]

Try activeworkbook..instead of thisWorkbook

[mdmackillop]

Hi Rich
Welcome to VBAX
Assuming you're working on a network, you can put an Add-In (xla file) on the server. Each user has to install the add-in; but DO NOT copy to the local machine. You then just need to update the add-in code in one location.
Regards
MD

[lucas]

Shouldn't he still be using activeworkbook instead of thisworkbook Malcolm....? Whether it's an addin or just code in a workbook in xlstart so the code is available.

[RIchHW]

Thanks so much. Changing to ActiveWorkbook did the trick. I'm glad I joined the forum.

[lucas]

Hi Rich,
If your problem is solved for this thread..please mark it solved using the thread tools at the top of the page.

ps. If you select your code when posting and hit the vba button it will format the code for easier reading in the forum...I edited your first post.

[mdmackillop]

Hi Steve,
No problems with your solution. Mine is an alternative approach which avoids User action when a code update is required
Regards
Malcolm

[lucas]

I'm glad I joined the forum.

Almost forgot to welcome you to VBA Express.....Welcome

MD,

Hi Steve,
No problems with your solution. Mine is an alternative approach which avoids User action when a code update is required

I agree, I use addins a lot now. Nothing in personal except when I'm testing something...
Much easier to update one addin on a server than several on personal machines.
One thing I learned though was that if you reference thisworkbook in your addin...it operates on the addin. I learned the hard way to use activeworkbook in my addins.