You're right Bernie - as soon as it starts to insert the code Excel crashes.

Does anyone have any other suggestions?

"Bernie Deitrick" wrote:

Giz,

You can do it with code, but I often have Excel die when doing so.

That said, try the macro below, with the activeworkbook being workbook A or B - I wasn't sure which
needs the new sub. And note that, as written, the code will remove any code in the ThisWorkbook
object's codemodule....

Also, this code requires a reference to MS VBA Extensibility.

HTH,
Bernie
MS Excel MVP

Sub Test()
Dim myBook As Workbook
Dim myVBA As VBIDE.VBComponent
Set myBook = ActiveWorkbook
Set myVBA = myBook.VBProject.VBComponents(myBook.CodeName)

On Error GoTo NoLines
With myVBA.CodeModule
.DeleteLines 1, .CountOfLines
End With

NoLines:

With myVBA.CodeModule
.InsertLines 1, "Private Sub Workbook_Open()" & Chr(10) & _
"Msgbox ""Hi there from your new macro""" & Chr(10) & _
"End Sub"
End With

End Sub


"Gizmo63" wrote in message
...
Here's the challenge.
The user's workbooks all rely on macros.
To simplify updating of macros and bug fixing all the macros are kept in a
central storage file.

"workbook A" has it's buttons linked to "Macro Storage" as does "workbook B"
etc etc
So when the user hits a button in "workbook A" it opens "Macro Storage" as
read-only and executes the macro.

The mods that I'm making require some coding to be added to the users
workbook. (Essentially a run-on-load subroutine).

When the mod runs from "Macro Storage" is there any way to create a
"Private Sub Workbook_Open()" in the users workbook and insert the code?

Thanks in advance.

Giz