richardpfister
New Member
- Joined
- Nov 18, 2016
- Messages
- 7
I know how to declare public variables and then reference them from other workbooks but I am unable to make this work when the originating workbook name is the variable. Here is my scenario:
User opens Workbook1 and runs Macro1, which does the following
Macro2 (in wb2) is called and runs fine until I reference wb1
It throws an error because wb1 is empty. I want to late-bind it (e.g. Application.Workbooks("Workbook1.xls").variablename) but that requires the very details I'm trying to store in a variable.
Someone's going to ask so here's my end goal: I have 20 workbooks used by multiple users. I want to maintain one master file of macros accessible from the 20. I first tried having the 20 files replace their own VBProjects with those in the master file; that was messy and unsuccessful. So now I'm trying to have the 20 files open the master workbook and run the macros from there. So there's a kick off program to open the master and then run the macros in it. The problem I'm having is that the 20 files must all be named differently, so I don't know how to refer back to the originating file once the master is open and running.
Hopefully this all makes sense and somebody has some ideas. Thanks very much in advance for your time.
- Richard</code>
User opens Workbook1 and runs Macro1, which does the following
Code:
Public wb1 As Workbook
Public wb2 As Workbook
Sub Macro1()
Set wb1 = ActiveWorkbook
Workbooks.Open ("Workbook2.xlsm")
Set wb2 = ActiveWorkbook
Application.Run wb2.Name & "!Macro2"
End Sub
Macro2 (in wb2) is called and runs fine until I reference wb1
Code:
Sub Macro2()
wb1.Activate <code breaks="" here,="" runtime="" error="" 424:="" object="" required="">''it throws a 424 object required error here
It throws an error because wb1 is empty. I want to late-bind it (e.g. Application.Workbooks("Workbook1.xls").variablename) but that requires the very details I'm trying to store in a variable.
Someone's going to ask so here's my end goal: I have 20 workbooks used by multiple users. I want to maintain one master file of macros accessible from the 20. I first tried having the 20 files replace their own VBProjects with those in the master file; that was messy and unsuccessful. So now I'm trying to have the 20 files open the master workbook and run the macros from there. So there's a kick off program to open the master and then run the macros in it. The problem I'm having is that the 20 files must all be named differently, so I don't know how to refer back to the originating file once the master is open and running.
Hopefully this all makes sense and somebody has some ideas. Thanks very much in advance for your time.
- Richard</code>