PDA

View Full Version : How to read values from a dynamic form with dynamic text box names?



lionne
12-09-2011, 07:13 AM
Hi everyone,
I have a problem: recently I have finished the routine, which allows me to create dynamic forms with dynamic number of frames, labels and text boxes. Now I have a problem, that I need to save the values, that will be entered in the form. Add I have no idea how can I refer to a text box...

Here is one example:
Normally I'd write something like:
ws.Cells(1,1) = UserForm1.TextBox1.Value
where TextBox1 is the name of the Text Box on the form.
Now, I have dynamic text box names, e.g. they will be taken from a pool. There are 156 names in the pool. Doesn't really make sense to code 156 cases. Is there a way to keep it dynamic, saying like: just take the value of the first text box, write it there, then take another one and so on...

Would be really greateful to any hint..

Lionne

Aflatoon
12-09-2011, 07:23 AM
If you use a naming convention when you add the controls, it would be quite simple to refer to them afterwards by name (using Me.Controls("Textbox1") or similar). If not, then you will almost certainly require a loop through the controls.

lionne
12-09-2011, 07:27 AM
Hi Aflatoon, thanks for the reply.
The thing is, I don't use the Excel naming convention. I name them with the tags from the pool. That is why this problem comes. How do you mean it with the loop, I can't really imagine the logic...

Aflatoon
12-09-2011, 07:32 AM
If you have named them from a pool of names, why can you not simply use those names when retrieving the values?

This seems to me like a strange way to develop, but in any event, we cannot make specific suggestions without a more specific idea of what you have done to create the controls (in other words, seeing some code)

lionne
12-09-2011, 07:44 AM
First maybe a little about the idea: I have a worksheet with predefined values. Based on that values, the form will be created for each row of values

Here is the fragment of the code for form creation:

Set NewFrame1 = MetaParList.Designer.Controls.Add("Forms.Frame.1")
With NewFrame1
.Top = 15
.Left = 5
.Width = 580
End With

For iRow5 = 1 To field
Set NewTextBox = NewFrame1.Controls.Add("Forms.TextBox.1")
With NewTextBox
.Name = tm.Cells(iRow5, 2)
.Width = 450
.Height = 15
.Top = 25 * iRow5
.Left = 100
End With

Set NewLabel = NewFrame1.Controls.Add("Forms.Label.1")
With NewLabel
.Width = 90
.Height = 15
.Top = 25 * iRow5
.Left = 10
.Caption = tm.Cells(iRow5, 4)
End With


As you see, the number of Text Boxes and their Names are dynamic. Then, when the form appears, the user enters some values in the Text Boxes, clicks OK and the routine goes further and the next form with new text boxes will be created and so on.

So, the values entered from user need to be saved in some worksheet. And this is where the problem comes: how to refer to a text boxes, if their names are dynamic.

mikerickson
12-09-2011, 08:09 AM
You could do something like this when creating

Set NewTextBox = NewFrame1.Controls.Add("Forms.TextBox.1")
With NewTextBox
.Tag = tm.Cells(iRow5, 2).Address: Rem changed <<<
.Width = 450
.Height = 15
.Top = 25 * iRow5
.Left = 100
End With

Set NewLabel = NewFrame1.Controls.Add("Forms.Label.1")
With NewLabel
.Width = 90
.Height = 15
.Top = 25 * iRow5
.Left = 10
.Caption = tm.Cells(iRow5, 4)
End With
End With
And this when writting to the worksheet
Dim oneControl As Object

For Each oneControl In Userform1.Controls
If TypeName(oneControl)= "TextBox" Then
With oneControl
Range(.Tag) = .Text
End With
End If
Next oneControl
Even with a class module approach, storing the associated cell address at the time of creation helps keep track of what the control is supposed to be doing.

Aflatoon
12-09-2011, 08:13 AM
It still appears to me that you know the name of the textbox - it is the value of tm.Cells(iRow5, 2)
So you can either refer back to that, or drop those cells into an array and use that for both the creation of the controls and reading the values back again.

mikerickson
12-09-2011, 08:54 AM
With added controls the name isn't as important as the puropose for which it was added.

Rather than keeping track of names, I find it easier to mark each control with its purpose. e.g. allow the user to edit cell in column G.
The .Tag property is my prefered location for storing that info. (E.G. .Tag = sheet2.Range("G:G").Address)

That approach allows the control to be "self contained" so I don't need to know the name of the box for Names (column G) and the name of the box for Phone Number (column H). Each box "knows" what it is for and I can read that for each box.

Aflatoon
12-09-2011, 09:02 AM
For controls added when the form is loaded, I would tend to agree with you. The fact that these controls are being added to the Designer made me think they were a little more permanent, in which case I would name them appropriately too.

lionne
12-28-2011, 01:39 AM
Hi guys, sorry for replying such late.. In fact, I could solve the issue a little differently. In order to refer to the TextBox, I just use:
Me.Controls(" & field & ").Value
where field is a dynamic TextBox Name.
Thanks a lot anyway!