PDA

View Full Version : Solved: Versatile Form



blue_bogdan
05-07-2007, 03:30 AM
Hello...

I must say I am prety new around here... but still found some really nice and usefull stuf.

But let's just say i ran into a small issue... and here it goes...

I'm trying to construct an UserForm that can show a number of Labels and related TextFields based on a number previously establised...

Thank You in advance...

mdmackillop
05-07-2007, 03:47 AM
Rather than construct the form using VBA (it can be done but a bit tedious), Add all the Labels and Fields to the form with Visible set to false. Use the Initalise event to set to True the Controls you wish to be Visible.

JimmyTheHand
05-07-2007, 03:55 AM
Hi and Welcome to VBAX :hi:

I propose two options for you.
1.)
You create a fixed number of labels and textboxes in design time, which are, by default, invisible. Then you can make as many of them visible, in run time, as you need. The disadvantage is that you must limit the number of controls in design time.

2.)
You can add controls to a form in run-time, set their position and size, and even query their values. You must give them unique names, preferably ones that can be handled by a loop.
Example code:

'to create and position a textbox
Dim Ctrl As Control, i As Long
For i = 1 to 10
Set Ctrl = Form1.Controls.Add("Forms.TextBox.1") 'D?tum
With Ctrl
.Left = 10
.Width = 80
.Top = 20 * i
.Height = 18
.Value = "I'm a run-time created textbox No. " & CStr(i)
.Name = "RunTime_TB" & CStr(i)
End With
Next

'to query the value of this textbox, after it had been created
Dim s1 As String, s2 as String
s1 = Form1.Controls("RunTime_TB1")
s2 = Form1.Controls("RunTime_TB2")
The disadvantage is that you can't assign event macros to a run-time created control.

HTH

Jimmy

blue_bogdan
05-07-2007, 04:07 AM
Rather than construct the form using VBA (it can be done but a bit tedious), Add all the Labels and Fields to the form with Visible set to false. Use the Initalise event to set to True the Controls you wish to be Visible.

I agree.... thougt of it... but i see this olution feasable to about 10-20 pairs (Label - TextBox)....

I need this to do some 40 pairs... just to begin with....

10x.

mdmackillop
05-07-2007, 05:22 AM
That's a lot of data on the form. Is it appropriate to consider other solutions? What about 2 column listbox for example.

blue_bogdan
05-07-2007, 06:36 AM
That's a lot of data on the form. Is it appropriate to consider other solutions? What about 2 column listbox for example.

What exactly do you mean...

I was not clear enough... after this kind of form is created i want the user to fill in the text boxes so the info can be used further in the program...

Please help... :help:help:help

mdmackillop
05-07-2007, 09:35 AM
You say 40 pairs ..just to begin with.
Is this just to enter data? How about the built in Data Form? Wihout your paramaters and intentions, I can't think what more to suggest.

lucas
05-07-2007, 09:40 AM
or multipage form...

blue_bogdan
05-08-2007, 02:51 AM
Let me redifine the problems...
I have a function that works like this

f(X,Y)=(X1-Y1)*C1+(X2-Y2)*C2+....+(Xn-Yn)*Cn

To start with i do not know the n to begin with... it comes from a number that the user puts in.
The C comes from other calculations and is related to n but in another way.

I want the users to input the X and Y values, because there is no mathematical relationship between them and n.

Hope you understand what i mean.

Thank you

mdmackillop
05-08-2007, 05:26 AM
I don't see the benefit of a userform here, but if you want one created, then use Jimmy's code. Personally I would dress up a spreadsheet with some colours and borders which is much simpler to code and more flexible in handling the data.

blue_bogdan
05-08-2007, 07:45 AM
I don't see the benefit of a userform here, but if you want one created, then use Jimmy's code. Personally I would dress up a spreadsheet with some colours and borders which is much simpler to code and more flexible in handling the data.

I already tried the method you sugested, but my target users found it rather difficult to work with the file... so i decided to build a form for them to do what the file did, put it in an xla file and let them work with it...

10x for all your sugestions... and especialy Back again... I tried the method JimmyTheHand supplied and it does the job... I'll just change the thread to solved....

10x again.