PDA

View Full Version : Userform as a variable and moving data



matthew230
05-24-2008, 07:12 AM
Hi All,

I'm hoping that I can get some help with a frustrating problem.

I need to transfer information from one text box on a userform to another on a seperate userform, although there are alot of various combinations. For example anyone the destination userform and the source userform all have at least 5 textboxes, all of which need to be interchangable.

Therefore I need to use variables. However, it is not quite working out just yet.

So instead of:

userform1.textbox1.value = userform2.textbox1.value

I need the userform and textbox elements to be variables that are defined elsewhere.

Any suggestions gratefully received !!

Matt

Norie
05-24-2008, 07:19 AM
Matt

It might help if you gave us some more information.

Where would these variables be defined?

When do you want to transfer the information?

Generally you can refer to controls on a userform like this.

UserForm2.Controls("Textbox1").Value = UserForm1.Controls("Textbox2").Value

matthew230
05-24-2008, 07:23 AM
Hi, and thanks for your reply.

I have got the textbox elements done, but didn't want to confuse the original post.

The part I'm really struggling with is the using a variable in place of the userform name....

The first userform is the 'target' userform, beside each textbox within the target userform is a command button. This command button establishes the target userform and the target textbox variables, and opens the second userform. The second userform is the source userform. There are a similar arrangement of text boxes and buttons, and the pressing of these hides the source userform, establishes the source userform and source textbox variables and then calls the procedure to make the copy. The copy fairly straightfoward, the only complication being that the target must retain what is already there, plus any additional text from the source.

Matt

Norie
05-24-2008, 08:09 AM
Matt

What are you actually trying to achieve?

It sounds like a rather convoluted approach.

matthew230
05-24-2008, 08:46 AM
This is for an application that constructs careplans for care staff to refer to.

Within these care plans there many occasions when the same instructions are utilised repeatedly. Therefore, I am trying to setup a 'quick add' type feature. There are currently about 45 entry textboxes over 10 userforms.

For each 'section' there are 3 - 5 textboxes for entry purposes. Each 'section' then has a second userform that contains 5 pre-typed textboxes that can be transferred over to the current entry form in the relevant section. Because for example you may want to add the pretyped text from textbox4 in the second userform to the 2nd testbox in the entry form, or any combination of these.

The sticking point is being able to use a variable to refer to a userform.

If there is no solution to this, then I will have to look at another method.

The problem with simple drop downs is firstly the textbox entry will need to be able to add text from perhaps more than one pretyped source box, and keep any manually added text. Also it would be nice to have a feature that allows password users to update the 'pretyped' texts.

This stuff is really to explain in a few lines !! :think::think::think::think:

Norie
05-24-2008, 09:38 AM
Matt

I'm sorry but it sounds to me like you are overcomplicating things.

10 userforms, 45 textboxes = 450 controls to deal with.:eek:

And I don't get your comment on dropdowns.

What do you mean by 'the textbox entry will need to be able to add text from perhaps more than one pretyped source box, and keep any manually added text'?

PS Oh, and please don't cross post without at least supplying a link.:)

matthew230
05-24-2008, 12:54 PM
Hi Norie,

6 hours, and putting google to the test, half watching Michael J Fox in the Secret of My Success, and a partial glance up at Superman 2...kneel before zod !!! ...has led me to the conclusion that I need to find a work around....so I have.

It meant more typing but it is all functional now.

Thanks for all your help regadless.

Matt

PS....up to about 185 controls !!

mikerickson
05-24-2008, 01:32 PM
Given the name of an loaded userform, the only way that I can think to return that as a variable is
Dim FoundUF as Object, oneUF as Object

For Each oneUF in Userforms
If oneUF = "userformName" Then
Set FoundUF = oneUF
Exit For
End If
Next oneUF

The reason is that there can be multiple instances of a particular userform, so names are not used to index the Userforms collection, i.e. there is no UserForms("userformName")

Also, the collection UserForms is only those that are loaded. One needs to look through the VBComponents to find the unloaded userforms, (but that returns the UF as a VBcomponent, NOT as a userform) i.e. VBComponents("myUserform") does not have a .Controls property.