using forms and modules in multiple workbooks

  • Thread starter Thread starter scrabtree23
  • Start date Start date
S

scrabtree23

I want a VBA code to use in WorkBook A that will look in
another open workbook, WorkBook B and use that WorkBook's
Forms and modules???
 
First, in workbook B in VBA, go to the Tools menu, choose VBA Project
Properties, and give the project a unique name like ProjB. Then open
workbook A in VBA and go to the Tools menu, choose References, and select
ProjB from the list. Workbook A now references workbook B.

Now, you can call normal subs and functions in B from A as if they were part
of A. To avoid naming conflicts, and for good coding practice, you should
prefix the name of the procedure with the library name. E.g., in A,
ProjB.MacroName

For forms, it is a bit more difficult because forms can directly be called.
They are private to the workbook that contains them. If all you need to do
is show the form, but not access any of its controls, create a macro in B
like the following:

Public Sub ShowTheForm()
UserForm1.Show
End Sub

Then, call this macro from A with code like
ProjB.ShowTheForm

If you need full access to the form and its controls, you need a macro in B
that returns an instance of the form. For example, in B, use code like

Public Function GetForm1() As UserForm1
Set GetForm1 = UserForm1
End Function

Then, in A, call this function to get a reference to the form object.

Dim F As Object
Set F = ProjB.GetForm1()
F.Show


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top