Prev: Help with IIF statement in macro
Next: Export Table Records to txt file
From: David Macdonald on 28 May 2010 03:32
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?

--
WinXP - Office2003 (Italian)
From: JLGWhiz on 28 May 2010 15:13
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.


"David Macdonald" <DavidMacdonald(a)discussions.microsoft.com> wrote in
message news:731897E8-D668-4CC5-8F62-98071C17CFDC(a)microsoft.com...
>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?
>
> --
> WinXP - Office2003 (Italian)


From: David Macdonald on 3 Jun 2010 07:42
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?


--
WinXP - Office2003 (Italian)


"JLGWhiz" wrote:

> 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.
>
>
> "David Macdonald" <DavidMacdonald(a)discussions.microsoft.com> wrote in
> message news:731897E8-D668-4CC5-8F62-98071C17CFDC(a)microsoft.com...
> >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?
> >
> > --
> > WinXP - Office2003 (Italian)
>
>
> .
>
 | 
Pages: 1
Prev: Help with IIF statement in macro
Next: Export Table Records to txt file