Add code to Workbook Open on another Workbook

pingme89

Board Regular
Joined
Jan 23, 2014
Messages
170
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
This is what I usually use for a new file, have a try:
VBA Code:
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
 
Last edited:
Upvote 0
Solution
This is what I usually use for a new file, have a try:
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top