Passing data variables from one userform to another

[dsc]

Is this possible?

I have been using data from one userform in another, with this in the
UserForm_Activate() sub of UserForm10:

JString = UserForm4.ListBox1.Column(0)

Now I need to call UserForm10 from forms 3 and 11 as well, and have it act
on data in their listboxes.

In VBA, can you pass data variables from one form to another? If so, does
anybody know of an example?

Thanks for any assistance.

 

[Helmut Weber]

Hi,
well, I'd say, to pass data from one userform to another,
you don't need variables at all.

I got 3 userforms. Each userform has a textbox.
I start userform1, which has a button with code
"userform2.show", which has a button with code "userform3.show".
I type "test" in the textbox of the now active userform3,
which has a button with code:

UserForm1.TextBox1.Text = Me.TextBox1.Text
UserForm2.TextBox1.Text = Me.TextBox1.Text

Of course, unless you specify a location for the userforms,
they would be on top of each other. So after starting userform1,
move it manually to some other place, the same applies to userform2.

Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[dsc]

Hi.

The problem is that I need to be able to copy directly from a listbox in one
of three different forms, without typing.

So, when activating UserForm10, sometimes I need

JString = UserForm4.ListBox1.Column(0)

and other times I need

JString = UserForm3.ListBox1.Column(0) or
JString = UserForm11.ListBox1.Column(0)

When I put JString = UserForm4.ListBox1.Column(0) in UserForm_Activate() sub
of UserForm10, it works fine from UserForm4, but when I call it from the
other two forms, obviously, it doesn't work, because it's trying to put in
the data from UserForm4.

I need some way to tell it on the fly which userform to look for.

Something like (and I know this isn't right; it's just conceptual)

UserForm_Activate(UserForm)

Called by a command like UserForm10.Show(UserForm3)

So I can tell UserForm_Activate() sub of UserForm10 which userform's listbox
to look at.

 

[Jezebel]

Use form variables, rather than referring to the forms directly:

Dim pForm as form

Select case .....
Case 1
Set pForm = UserForm4
case 2
set pForm = UserForm11
:

JString = pForm.Listbox1.Column(0)

 

[JBNewsGroup]

Hi Helmut,

Another possibility is to use a single form and either a multipage or tab
strip control. I have had the same requirement in the past and the
multipage scenario seemed to work well. I took all the controls that
processed "common data" and put them in a single frame. Outside of the
frame I put a multipage control, each page of which contained the
"non-common data". I then made a module for each page (so as not to have a
hugh pile of code in the form) and called the module with Me as the first
module argument. That way I could access each form control without directly
using the form name (see Jezebel's comment). To be honest, I could not get
the tab strip to work the way I wanted. I am sure that it is another
(probably more efficient) way to accomplish the task.

Jerry Bodoff

 

[dsc]

That looks like a good idea, but I'm still left with the problem of telling
UserForm10 which case to use. I 'm calling it from userforms 3, 4, or 11,
and somehow I have to tell userform10 which one to use.

 

[Jezebel]

Hard to make suggestions without knowing the logic of what you're trying to
do.

 

[dsc]

Hard to make suggestions without knowing the logic of what you're trying
to
do.

Okay.



I have an access database that has three fields: Japanese term, English
term, and Client. I call it from Word using macros and forms.



One thing I do is look to see if a particular client has an English entry
for a particular Japanese term. Failing that, I can see if any client has an
entry for that term, or do a wildcard search and see if there are any
entries for any client containing the term. I can also go backwards and
check an English term.



UserForm3 does the search for the exact term for any client, and lists
English, Japanese, and client for all hits in a listbox.

UserForm4 does the wildcard search for all occurences of the term, and lists
English, Japanese, and client for all hits in a listbox.

UserForm11 does the search for an English term and lists the English,
Japanese, and client in a listbox.



One of the command buttons on these three user forms is "modify entry." This
pops up userform 10, which has 3 text boxes: one for the J, one for the E,
and one for the client. Changing the text in these boxes and hitting
"change" changes the entry in the Access database to match.



At first, I only modified entries from userform4. At that point, this code
worked fine:



Private Sub UserForm_Activate()



JString = GlossaryForm4.ListBox1.Column(0)
EString = GlossaryForm4.ListBox1.Column(1)
CString = GlossaryForm4.ListBox1.Column(2)



With tbxEntry
GlossaryForm10.JapaneseBox.Text = JString
GlossaryForm10.JapaneseBox.SetFocus
GlossaryForm10.JapaneseBox.SelStart = 0
GlossaryForm10.JapaneseBox.SelLength =
Len(GlossaryForm10.JapaneseBox.Text)
GlossaryForm10.EnglishBox.Text = EString
GlossaryForm10.EnglishBox.SelStart = 0
GlossaryForm10.EnglishBox.SelLength =
Len(GlossaryForm10.EnglishBox.Text)
GlossaryForm10.ClientBox.Text = CString
GlossaryForm10.ClientBox.SelStart = 0
GlossaryForm10.ClientBox.SelLength =
Len(GlossaryForm10.ClientBox.Text)
End With
End Sub



Now, though, I also want to call userform10 and modify entries from
userform3 and userform11, and



JString = GlossaryForm4.ListBox1.Column(0)
EString = GlossaryForm4.ListBox1.Column(1)
CString = GlossaryForm4.ListBox1.Column(2)



doesn't allow me to do that. I need to pass something from the calling
userform to tell userform10 which form is calling it, so that it knows
whether to do



JString = GlossaryForm4.ListBox1.Column(0)

or
JString = GlossaryForm3.ListBox1.Column(0)

or
JString = GlossaryForm11.ListBox1.Column(0)



Now, in C you would do something like, UserForm10.Show(UserForm3)



And in userform10, UserForm_Activate() would have a variable:
UserForm_Activate(UserForm)



That way, userform10 would know which userform was calling it, and would
look for listbox1 in that form.



Can you do that in VBA?



Any assistance appreciated.