MS Office Forum / Word / Programming / December 2004
Passing data variables from one userform to another
|
|
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.
|
|
|