For Each Statement to loop across bunch of textbox values

Question

I have a bunch of textboxes which will be populated with values selected from a dropdown.

Lets say out of 10 textboxes, values were populated in 5: Rinv1 to Rinv5.

How can I loop through these and collect those values?

I am trying to use a for each loop and collect those values in an array and then store those values in a field in my DB.

Rolls = Array(Me.Rinv1, Me.Rinv2, Me.Rinv3) Dim RollName As Variant 'iterating using For each loop. For Each Item In Rolls RollName = RollName & Item Next Forms![LabelSHEETER2].JOB = RollName

Some syntax error.

Lee Mac · Accepted Answer · 2019-06-28 17:37:37Z

2

I would suggest iterating over the control names rather than the control objects, for example, something along the lines of the following:

Dim Itm Dim RollName As String For Each Itm in Split("Rinv1 Rinv2 Rinv3") RollName = RollName & Me.Controls(Itm).Value Next Itm Forms![LabelSHEETER2].JOB = RollName

answered Jun 28, 2019 at 17:37

Lee Mac

16.1k6 gold badges38 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Doug Coats Over a year ago

better answer here!

NewAtSQL Over a year ago

Rinv1, Rinv2, Rinv3 and so on are textbox names...not the actual value. The value might change. When you say control names over control object, what does that mean? I apologize if that's a very naive question.

NewAtSQL Over a year ago

ok, i read some definations online and tried to follow your code. It works like a charm! Thanks a lot :)

Lee Mac Over a year ago

@NewAtSQL You're most welcome. For completeness: each Control object (textbox, listbox, combobox etc.) in the Controls collection of a Form has a Name property which may be used to acquire the object from the Controls collection. My suggestion was to iterate over the names of such objects, rather than the objects themselves.

horst · Accepted Answer · 2019-06-28 17:43:29Z

Here is a simple example of how to get the values of all TextBoxes and combining them into a string

Private Sub CommandButton1_Click() Dim ctrl As Control Dim str As String For Each ctrl In UserForm1.Controls If TypeName(ctrl) = "TextBox" Then str = str & ctrl.Text End If Next ctrl MsgBox str End Sub

sleep.sauce · Accepted Answer · 2019-06-28 19:47:06Z

I would loop through the Form controls and check for name matches. This way, you don't need to leave the Form design window again to change your code or add objects to an array.

Dim ctl As Control Dim strRollName As String For Each ctl in Me.Controls If ctl.Name Like "Rinv*" Then strRollName = strRollName & ctl.Value & " " End If Next ctl

Collectives™ on Stack Overflow

For Each Statement to loop across bunch of textbox values

3 Answers 3

4 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Related