how to loop through textboxes

Giganews Newsgroups
Subject: how to loop through textboxes
Posted by:  Roady Mayhem (s.hoitin…@gmail.com)
Date: Thu, 16 Jul 2015

Hi all,

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

Next i

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,

Sybolt

Replies