|Subject:||how to loop through textboxes|
|Posted by:||Roady Mayhem (s.hoitin…@gmail.com)|
|Date:||Thu, 16 Jul 2015|
On a form I have some 5 textboxes with names txtrefno1, txtrefno2, ... to txtrefno5.
I also have a textbox with the name txtrefno.
Now I want to loop through the textboxes, and put the values in a table.
So far, my code looks like this:
r = Selection.CurrentRegion.Rows.Count
For i = 1 To revs
Dim b As Control, c As Control
Set b = Me.txtRefno
Set c = b & i
ActiveCell.Offset(r, 0).Value = Me.txtRefNo1.Value
ActiveCell.Offset(r, 1).Value = Me.lblNewAssignment.Caption
ActiveCell.Offset(r, 2).Value = c.Value
ActiveCell.Offset(r, 3).Value = Me.cboLSP.Value
ActiveCell.Offset(r, 4).Value = Me.cboLaneID.Value
ActiveCell.Offset(r, 5).Value = Me.cboCTM.Value
r = r + 1
You see that I'm hoping to create me.txtrefno1 ( and me.txtrefno2 etc, depending on the value in varaible revs ) with the line
set c = b & i
This however will not work.
Is there a way to let Excel understand that I want to refer to an existing textbox where the name has to be created in code?
Please, the loop would save a lot of extra code.
Any help is greatly appreciated.
Tanks in advance,