referencing controls in userform from a different workbook

D

David Macdonald

I want to be able to call up the same subform from a number of different
forms in different workbooks.
I have "customers.xls" with a macro to show a form that lists all the
customer names.
The user can be using various forms in different workbooks that need the
exact customer name. So with Application.Run and refernce to the workbook and
its macro I can open the form.
When the user double-clicks an item in the listbox the value should get put
into a textbox on the form that called the subform.
I'm missing the final stage - how do I reference the target textbox so that
the data will go where I want it?
 
J

JLGWhiz

Reverse the path you used to get to the list box that is clicked.

Workbooks("Customers.xls").<userformname>.<controlname> =
<listboxname>.value

The form needs to be in show mode when you do it or the control cannot be
accessed.
 
D

David Macdonald

Thanks for the response but I'm still unable to get it to work. I have things
set up like this:
workbook: Customers.xls
userform: FindCustomerForm
control: CustomerListbox

The first workbook I want to have opening the form and getting info from it
is "Orders":
workbook: Orders.xls
userform: NewOrderForm
control: CustomerName
Double-clicking in the CustomerName textbox opens the FindCustomerForm. I
find the right customer and double-click the listbox item.
At that point FindCustomerForm should transfer the value to CustomerName on
NewOrderForm then unload itself.

If the two forms are in the same workbook I can get this to work perfectly,
using a public string "ThisControl" to retain the name of the control that
called the customer list for example:
Private Sub CustomerName_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ThisControl = "CustomerName"
FindCustomerForm.Show
End Sub

and

Private Sub CustomerListbox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
NewOrderForm.Controls(ThisControl).Value = CustomerListbox.Value
ThisControl = ""
Unload Me
End Sub


My problem is that I want to call FindCustomerForm from Complaints.xls,
Shipments.xls, and ChristmasGreetings.xls too.

So how do I construct the general purpose string (including network path)
that will get the customer name to whatever control called it?
Do I need to put "Public ThisControl As String" in both workbooks?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top