software

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

Does anyone have any other suggestions?

quot;Bernie Deitrickquot; wrote:

gt; Giz,
gt;
gt; You can do it with code, but I often have Excel die when doing so.
gt;
gt; That said, try the macro below, with the activeworkbook being workbook A or B - I wasn't sure which
gt; needs the new sub. And note that, as written, the code will remove any code in the ThisWorkbook
gt; object's codemodule....
gt;
gt; Also, this code requires a reference to MS VBA Extensibility.
gt;
gt; HTH,
gt; Bernie
gt; MS Excel MVP
gt;
gt; Sub Test()
gt; Dim myBook As Workbook
gt; Dim myVBA As VBIDE.VBComponent
gt; Set myBook = ActiveWorkbook
gt; Set myVBA = myBook.VBProject.VBComponents(myBook.CodeName)
gt;
gt; On Error GoTo NoLines
gt; With myVBA.CodeModule
gt; .DeleteLines 1, .CountOfLines
gt; End With
gt;
gt; NoLines:
gt;
gt; With myVBA.CodeModule
gt; .InsertLines 1, quot;Private Sub Workbook_Open()quot; amp; Chr(10) amp; _
gt; quot;Msgbox quot;quot;Hi there from your new macroquot;quot;quot; amp; Chr(10) amp; _
gt; quot;End Subquot;
gt; End With
gt;
gt; End Sub
gt;
gt;
gt; quot;Gizmo63quot; gt; wrote in message
gt; ...
gt; gt; Here's the challenge.
gt; gt; The user's workbooks all rely on macros.
gt; gt; To simplify updating of macros and bug fixing all the macros are kept in a
gt; gt; central storage file.
gt; gt;
gt; gt; quot;workbook Aquot; has it's buttons linked to quot;Macro Storagequot; as does quot;workbook Bquot;
gt; gt; etc etc
gt; gt; So when the user hits a button in quot;workbook Aquot; it opens quot;Macro Storagequot; as
gt; gt; read-only and executes the macro.
gt; gt;
gt; gt; The mods that I'm making require some coding to be added to the users
gt; gt; workbook. (Essentially a run-on-load subroutine).
gt; gt;
gt; gt; When the mod runs from quot;Macro Storagequot; is there any way to create a
gt; gt; quot;Private Sub Workbook_Open()quot; in the users workbook and insert the code?
gt; gt;
gt; gt; Thanks in advance.
gt; gt;
gt; gt; Giz
gt;

Gizmo,

The better way to do it is to have a template with all the code in it already, open the template,
copy the worksheets over from the existing workbook, kill the existing workbook, and save the
template with the original workbook's name. Keeps Excel from crashing.

Let us know if you need help with the code.

HTH,
Bernie
MS Excel MVPquot;Gizmo63quot; gt; wrote in message
...
gt; You're right Bernie - as soon as it starts to insert the code Excel crashes.
gt;
gt; Does anyone have any other suggestions?
gt;
gt; quot;Bernie Deitrickquot; wrote:
gt;
gt;gt; Giz,
gt;gt;
gt;gt; You can do it with code, but I often have Excel die when doing so.
gt;gt;
gt;gt; That said, try the macro below, with the activeworkbook being workbook A or B - I wasn't sure
gt;gt; which
gt;gt; needs the new sub. And note that, as written, the code will remove any code in the ThisWorkbook
gt;gt; object's codemodule....
gt;gt;
gt;gt; Also, this code requires a reference to MS VBA Extensibility.
gt;gt;
gt;gt; HTH,
gt;gt; Bernie
gt;gt; MS Excel MVP
gt;gt;
gt;gt; Sub Test()
gt;gt; Dim myBook As Workbook
gt;gt; Dim myVBA As VBIDE.VBComponent
gt;gt; Set myBook = ActiveWorkbook
gt;gt; Set myVBA = myBook.VBProject.VBComponents(myBook.CodeName)
gt;gt;
gt;gt; On Error GoTo NoLines
gt;gt; With myVBA.CodeModule
gt;gt; .DeleteLines 1, .CountOfLines
gt;gt; End With
gt;gt;
gt;gt; NoLines:
gt;gt;
gt;gt; With myVBA.CodeModule
gt;gt; .InsertLines 1, quot;Private Sub Workbook_Open()quot; amp; Chr(10) amp; _
gt;gt; quot;Msgbox quot;quot;Hi there from your new macroquot;quot;quot; amp; Chr(10) amp; _
gt;gt; quot;End Subquot;
gt;gt; End With
gt;gt;
gt;gt; End Sub
gt;gt;
gt;gt;
gt;gt; quot;Gizmo63quot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Here's the challenge.
gt;gt; gt; The user's workbooks all rely on macros.
gt;gt; gt; To simplify updating of macros and bug fixing all the macros are kept in a
gt;gt; gt; central storage file.
gt;gt; gt;
gt;gt; gt; quot;workbook Aquot; has it's buttons linked to quot;Macro Storagequot; as does quot;workbook Bquot;
gt;gt; gt; etc etc
gt;gt; gt; So when the user hits a button in quot;workbook Aquot; it opens quot;Macro Storagequot; as
gt;gt; gt; read-only and executes the macro.
gt;gt; gt;
gt;gt; gt; The mods that I'm making require some coding to be added to the users
gt;gt; gt; workbook. (Essentially a run-on-load subroutine).
gt;gt; gt;
gt;gt; gt; When the mod runs from quot;Macro Storagequot; is there any way to create a
gt;gt; gt; quot;Private Sub Workbook_Open()quot; in the users workbook and insert the code?
gt;gt; gt;
gt;gt; gt; Thanks in advance.
gt;gt; gt;
gt;gt; gt; Giz
gt;gt;
Thanks Bernie, but I'll be fine with coding that.

Had hoped for a nice 'enclosed' solution but hey, can't have everything!?!

Cheers anyway

Giz

quot;Bernie Deitrickquot; wrote:

gt; Gizmo,
gt;
gt; The better way to do it is to have a template with all the code in it already, open the template,
gt; copy the worksheets over from the existing workbook, kill the existing workbook, and save the
gt; template with the original workbook's name. Keeps Excel from crashing.
gt;
gt; Let us know if you need help with the code.
gt;
gt; HTH,
gt; Bernie
gt; MS Excel MVP
gt;
gt;
gt; quot;Gizmo63quot; gt; wrote in message
gt; ...
gt; gt; You're right Bernie - as soon as it starts to insert the code Excel crashes.
gt; gt;
gt; gt; Does anyone have any other suggestions?
gt; gt;
gt; gt; quot;Bernie Deitrickquot; wrote:
gt; gt;
gt; gt;gt; Giz,
gt; gt;gt;
gt; gt;gt; You can do it with code, but I often have Excel die when doing so.
gt; gt;gt;
gt; gt;gt; That said, try the macro below, with the activeworkbook being workbook A or B - I wasn't sure
gt; gt;gt; which
gt; gt;gt; needs the new sub. And note that, as written, the code will remove any code in the ThisWorkbook
gt; gt;gt; object's codemodule....
gt; gt;gt;
gt; gt;gt; Also, this code requires a reference to MS VBA Extensibility.
gt; gt;gt;
gt; gt;gt; HTH,
gt; gt;gt; Bernie
gt; gt;gt; MS Excel MVP
gt; gt;gt;
gt; gt;gt; Sub Test()
gt; gt;gt; Dim myBook As Workbook
gt; gt;gt; Dim myVBA As VBIDE.VBComponent
gt; gt;gt; Set myBook = ActiveWorkbook
gt; gt;gt; Set myVBA = myBook.VBProject.VBComponents(myBook.CodeName)
gt; gt;gt;
gt; gt;gt; On Error GoTo NoLines
gt; gt;gt; With myVBA.CodeModule
gt; gt;gt; .DeleteLines 1, .CountOfLines
gt; gt;gt; End With
gt; gt;gt;
gt; gt;gt; NoLines:
gt; gt;gt;
gt; gt;gt; With myVBA.CodeModule
gt; gt;gt; .InsertLines 1, quot;Private Sub Workbook_Open()quot; amp; Chr(10) amp; _
gt; gt;gt; quot;Msgbox quot;quot;Hi there from your new macroquot;quot;quot; amp; Chr(10) amp; _
gt; gt;gt; quot;End Subquot;
gt; gt;gt; End With
gt; gt;gt;
gt; gt;gt; End Sub
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Gizmo63quot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; Here's the challenge.
gt; gt;gt; gt; The user's workbooks all rely on macros.
gt; gt;gt; gt; To simplify updating of macros and bug fixing all the macros are kept in a
gt; gt;gt; gt; central storage file.
gt; gt;gt; gt;
gt; gt;gt; gt; quot;workbook Aquot; has it's buttons linked to quot;Macro Storagequot; as does quot;workbook Bquot;
gt; gt;gt; gt; etc etc
gt; gt;gt; gt; So when the user hits a button in quot;workbook Aquot; it opens quot;Macro Storagequot; as
gt; gt;gt; gt; read-only and executes the macro.
gt; gt;gt; gt;
gt; gt;gt; gt; The mods that I'm making require some coding to be added to the users
gt; gt;gt; gt; workbook. (Essentially a run-on-load subroutine).
gt; gt;gt; gt;
gt; gt;gt; gt; When the mod runs from quot;Macro Storagequot; is there any way to create a
gt; gt;gt; gt; quot;Private Sub Workbook_Open()quot; in the users workbook and insert the code?
gt; gt;gt; gt;
gt; gt;gt; gt; Thanks in advance.
gt; gt;gt; gt;
gt; gt;gt; gt; Giz
gt; gt;gt;
gt;
gt;
gt;