Value of Numerous TextBoxes using Loop

AmundalA

New Member
Joined
May 10, 2012
Messages
2
Hi!

This is my first post, so I hope someone can help a newbie like me:)

I have a simple userform with over 50 textboxes, named TextBox1, TextBox2, TextBox3 etc.....50.

I want the values (as string) of these textboxes back into my excel sheet.
Is there a way of using loops instead of manually defining every textbox?

Instead of:
'ActiveCell.Offset(0, 1).Select
'ActiveCell = UserForm1.TextBox1
'ActiveCell.Offset(0, 1).Select
'ActiveCell = UserForm1.TextBox2
'ActiveCell.Offset(0, 1).Select
'ActiveCell = UserForm1.TextBox3
and so on and so on....



This is how far I've gotten:
Dim ctrl As Object
Dim NextTextBox As Object
Dim Counter As Integer
Dim TextBox As Object

Counter = 0
TextBoxLoop:

Counter = Counter + 1

If Counter < 6 Then
NextTextBox = "UserForm1.TextBox" & Counter
ActiveCell.Offset(0, 1).Select
ActiveCell = "UserForm1.TextBox" & Counter ' Returns only the name of the textbox I want the values from. How to I get the values in the worksheet in Excel?

GoTo TextBoxLoop
End If




But this it only returns the Textbox-name in the cell instead for the actual content of the TextBox.
I know this is probably way of, but when I have managed to get a variable to be called the same as the name of the textbox I want values from...

Please don't let me manually go through 50 textboxes:)
Will be happy for any reply!
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276
Try something like this...

Code:
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Integer[/color]
    
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] 50
        ActiveCell.Offset(i - 1).Value = Me.Controls("TextBox" & i).Value
    [color=darkblue]Next[/color] i
 

AmundalA

New Member
Joined
May 10, 2012
Messages
2
thaaaank you! I was going crazy, and you make it so easy:)

Made just a small ajustment, because I wanted the result horisontaly displayed. I just put in the Rowoffset 0.

Dim i As Integer

For i = 1 To 50
ActiveCell.Offset(0, i - 1).Value = Me.Controls("TextBox" & i).Value
Next i


Thanks again for your help!!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,702
Messages
5,524,410
Members
409,576
Latest member
az168

This Week's Hot Topics

Top