Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / December 2004

Tip: Looking for answers? Try searching our database.

Passing data variables from one userform to another

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
dsc - 05 Dec 2004 02:27 GMT
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 - 05 Dec 2004 10:37 GMT
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/

>Is this possible?
>
[quoted text clipped - 10 lines]
>
>Thanks for any assistance.
dsc - 05 Dec 2004 13:46 GMT
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.

> Hi,
> well, I'd say, to pass data from one userform to another,
[quoted text clipped - 33 lines]
>>
>>Thanks for any assistance.
Jezebel - 05 Dec 2004 20:04 GMT
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)

> Hi.
>
[quoted text clipped - 63 lines]
> >>
> >>Thanks for any assistance.
dsc - 06 Dec 2004 13:33 GMT
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.

> Use form variables, rather than referring to the forms directly:
>
[quoted text clipped - 81 lines]
>> >>
>> >>Thanks for any assistance.
Jezebel - 06 Dec 2004 19:52 GMT
Hard to make suggestions without knowing the logic of what you're trying to
do.

> 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,
[quoted text clipped - 85 lines]
> >> >>
> >> >>Thanks for any assistance.
dsc - 07 Dec 2004 13:41 GMT
> 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.
JBNewsGroup - 05 Dec 2004 22:39 GMT
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

> Hi,
> well, I'd say, to pass data from one userform to another,
[quoted text clipped - 33 lines]
> >
> >Thanks for any assistance.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.