Add code to Workbook Open on another Workbook

[pingme89]

I am making a Workbook that can create another workbook with Macros and Buttons.
I have been able to Copy Modules from one Workbook to another, create worksheets with buttons assigned macros, and add named ranges to the new workbook.

However, I want to add Code to the Workbooks Open on the new workbook and I am not sure where to begin.

 

[rollis13]

This is what I usually use for a new file, have a try:

Option Explicit
Sub Add_Code()
    'Create in ThisWorkbook in a new file.
    Dim VBCodeMod As Object
    Dim LineNum As Long
    Dim Copybook As Workbook
    Set Copybook = Excel.Workbooks.Add            'new file
    Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule ' change module name as needed
    LineNum = VBCodeMod.CountOfLines + 1
    VBCodeMod.InsertLines LineNum, "Private Sub Workbook_Open()" & Chr(13) & Chr(13) _
                        & "    MsgBox ""This is a test""" & Chr(13) & Chr(13) _
                        & "End Sub"
    Set VBCodeMod = Nothing
End Sub

 

[pingme89]

This is what I usually use for a new file, have a try:

Option Explicit
Sub Add_Code()
    'Create in ThisWorkbook in a new file.
    Dim VBCodeMod As Object
    Dim LineNum As Long
    Dim Copybook As Workbook
    Set Copybook = Excel.Workbooks.Add            'new file
    Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule ' change module name as needed
    LineNum = VBCodeMod.CountOfLines + 1
    VBCodeMod.InsertLines LineNum, "Private Sub Workbook_Open()" & Chr(13) & Chr(13) _
                        & "    MsgBox ""This is a test""" & Chr(13) & Chr(13) _
                        & "End Sub"
    Set VBCodeMod = Nothing
End Sub

That worked. I was also trying to Make a Worksheet Object with code. I tried to modify your code but it didn't work.
After creating a new workbook with about 20 sheets, Dropdown menus, Buttons with macros, Conditional formatted cells, Named Ranges, Checkboxes etc, I got a phantom error which drove me crazy. So after the new workbook was created and I tried to change a value on a cell on the new workbook, I got an error message saying, " The cell or worksheet you are trying to modify is protected or something to that effect. The cell I was trying to change was actually an unlocked cells. Even if I unprotected the worksheet, that error message pops up and even cancelling it, still pops up for about 7 or 8 times.

One of the Macro modules I copied over to the new sheet has a Macro that restores the formatting for Worksheets("Q1"), Worksheets("Q2"), Worksheets("Q3") ... up to Worksheets("Q8").
If I run that module by clicking on a button in the new workbook that calls that macro, that phantom problem goes away.

Furthermore, I also had a 3rd workbook open. Once that error popped up, that same error message even happens when I try to modify a cell on the 3rd Workbook.

So this is telling me that this error is not on any particular workbook but with the Excel Application itself. So to try to remedy this issue, I was trying to run a Macro remotely on the newly created workbook. So I thought maybe that I could run the macro directly. This did not work. I believe it has something to do with Macro security or Trust.

I have actually been successful in invoking macro on another workbook before. However, that was done by actually adding code to a worksheet. In that worksheet module, I would call the macro. So this is why I am trying to add Worksheet Modules to invoke the Macro on the newly created workbook. So sorry this is so complicated.

 

[rollis13]

After creating a new workbook with about 20 sheets, Dropdown menus, Buttons with macros, Conditional formatted cells, Named Ranges, Checkboxes etc, I got a phantom error which drove me crazy.

Too much meat on the fire, can't follow you in this other criticality, sorry.