Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can one VB macro write another macro?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can one VB macro write another macro?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
format macro | Excel Discussion (Misc queries) | |||
How do I write a macro for a button to send user to a custom view? | Excel Discussion (Misc queries) | |||
How to write a macro?? | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
How do I write a macro for... | Excel Discussion (Misc queries) |