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

hi Sybolt,

Me.Controls("txtrefno" & i)

isabelle

Le 2015-07-16 07:43, Roady Mayhem a écrit :
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

I think declaring the variables should happen before the loop. e.g.

Dim b As Control, c As Control
For i = 1 To revs
Set ...


On 7/16/2015 6:08 AM, isabelle wrote:
hi Sybolt,
Me.Controls("txtrefno" & i)

isabelle

Le 2015-07-16 07:43, Roady Mayhem a écrit :
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



---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

hi,

like Mike as said, the variable declaration should be at the beginning of macro,

also, i do not see what is the variable revs?

isabelle


Le 2015-07-16 07:43, Roady Mayhem a écrit : 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