Passing a value from one userform to another (multiple) form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ketut - 06 Dec 2007 10:20 GMT

I have two userforms, form A and form B. Form A contains 10 textboxes
(txtbox1, txtbox2, ..., txtbox10), and form B contains a combobox and an OK
button.

What I want to achieve is as follow: whenever the user click one of the ten
textboxes if form A, form B will show. The user then select one value from
the combobox, click OK, and the selected value will be sent to the textbox
that was clicked initially. How do I make form B to recognize which textbox
that was clicked by the user?

P.S. Obviously, it will be easier if I can replace the ten textboxes in form
A with comboboxes, and eliminate the need to have form B altogether, but
unfortunately I do not have enough space in form A to do that. Each entry in
the combobox consist of 4 numbers followed by a description, and the
textboxes in form A have just enough width to show only the numbers.

Reply to this Message

Bob Phillips - 06 Dec 2007 11:09 GMT

Userform1

Public TB As MSForms.TextBox

Private Sub TextBox1_Enter()
Set TB = Me.TextBox1
UserForm2.Show
End Sub

Private Sub TextBox2_Change()
Set TB = Me.TextBox2
UserForm2.Show
End Sub

Userform2

Private Sub ComboBox1_Change()
UserForm1.TB.Text = Me.ComboBox1.Value
Me.Hide
End Sub

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have two userforms, form A and form B. Form A contains 10 textboxes
> (txtbox1, txtbox2, ..., txtbox10), and form B contains a combobox and an
[quoted text clipped - 16 lines]
> the combobox consist of 4 numbers followed by a description, and the
> textboxes in form A have just enough width to show only the numbers.

Reply to this Message

Ketut - 08 Dec 2007 08:31 GMT

Thank you very much, Bob. It really helped me.
But what if I have more than one userforms that is accessing the information
from userform 2? How do I make the combobox in userform 2 recognizes not just
the textbox that is calling calling it, but also the userform that contains
that textbox?

I tried the following:
public TB as MSforms.textbox

in userform 1: set TB = me.txtbox1

in userform 3: set TB = me.txtbox5

in userform 2: TB.value = me.combobox1.value

It didn't work :(

> Userform1
>
[quoted text clipped - 37 lines]
> > the combobox consist of 4 numbers followed by a description, and the
> > textboxes in form A have just enough width to show only the numbers.

Reply to this Message

Bob Phillips - 08 Dec 2007 17:52 GMT

You have to qualify the TB with the form that it is on.

Which form has the TB in this example?

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Thank you very much, Bob. It really helped me.
> But what if I have more than one userforms that is accessing the
[quoted text clipped - 62 lines]
>> > the combobox consist of 4 numbers followed by a description, and the
>> > textboxes in form A have just enough width to show only the numbers.

Reply to this Message

Ketut - 09 Dec 2007 00:03 GMT

Hi Bob, I apologize for couldn't make it clear.
Your earlier input helped me in solving my problem, but now the situation is
getting a bit more complex.

Now I have 3 userforms. Let's call them FormA, FormB, and InputForm. Both
FormA and FormB have 5 textboxes each, while InputForm has a combobox. What I
want to achieve this time is, whenever the user click any of the 10 texboxes,
the InputForm will be shown. After the user select one value from the
combobox in InputForm, this value then will be passed to the textbox that
called the InputForm.

My problem is I don't know how to make the combobox in InputForm to
recognize the form which contains the textbox that called it. I tried to to
the following:

public MyForm as UserForm
public TB as MSForms.TextBox

Then at FormA:
Private Sub TextBox1_Enter()
Set MyForm = FormA
Set TB = FormA.TextBox1
InputForm.Show
End Sub

Similarly in FormB:
Private Sub TextBox1_Enter()
Set MyForm = FormB
Set TB = FormB.TextBox1
InputForm.Show
End Sub

In InputForm:
Private Sub ComboBox1_Change()
MyForm.TB.Text = Me.ComboBox1.Value
Unload Me
End Sub

It didn't work..... :(

> You have to qualify the TB with the form that it is on.
>
> Which form has the TB in this example?

Reply to this Message

Bob Phillips - 09 Dec 2007 00:42 GMT

Ketut,

Best we turn the earlier suggestion on its head

FormA

Private Sub TextBox1_Enter()
Set InputForm.TB = Me.TextBox1
InputForm.Show
End Sub

Private Sub TextBox2_Enter()
Set InputForm.TB = Me.TextBox2
InputForm.Show
End Sub

etc.

FormB

Private Sub TextBox1_Enter()
Set InputForm.TB = Me.TextBox1
InputForm.Show
End Sub

Private Sub TextBox2_Enter()
Set InputForm.TB = Me.TextBox2
InputForm.Show
End Sub

etc.

InputForm

Public TB As MSForms.TextBox

Private Sub ComboBox1_Change()
TB.Text = Me.ComboBox1.Value
Me.Hide
End Sub

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi Bob, I apologize for couldn't make it clear.
> Your earlier input helped me in solving my problem, but now the situation
[quoted text clipped - 43 lines]
>>
>> Which form has the TB in this example?

Reply to this Message