VBA Filling up 500+ textboxes

Question

I have a userform with 500+ textboxes which are supposed to be filled up from some cells on some sheet. The part where i refer to sheets and cells is clear. But i am at a loss about how to fill 500+ textboxes without refering them one by one.

I do not want to do this:

Textbox1.value = sheet1.cells(x,y)
Textbox2.value = sheet1.cells(x+1,y)
.
.
.

I want to do this:

Textbox(x).value = sheet1.cells(x,y)

Of course in a loop

I have tried:

Private Sub UserForm_Activate()

Dim s As String
Dim m As Integer
Dim k As Integer
Dim l As Integer

s = "TextBox1"


For k = 1 To 10


s.Value = Sayfa9.Cells(l, m)
s = Replace(s, k, k + 1)
m = m + 1

Next k
end sub

Of course s.Value is wrong. How do i do this?

possible duplicate of [Looping through multiple Command Buttons to change their properties based on cell values](http://stackoverflow.com/questions/25266267/looping-through-multiple-command-buttons-to-change-their-properties-based-on-cel) ... and [Looping through many ComboBoxes by name](http://stackoverflow.com/questions/25282385/looping-through-many-comboboxes-by-name). Same principle applies to TextBoxes. — Jean-François Corbett, Dec 01 '14 at 08:31
No its not. As you can see there are way simpler ways to go for than what is said to duplicate. — user3146025, Dec 02 '14 at 06:23

score 0 · Answer 1 · answered Dec 01 '14 at 07:30

You could loop through all Controls within this Form, check whether they are TextBoxes, take the number of the TextBox from its name and then set its Value as required.

Like so:

Private Sub UserForm_Activate()
 y = 1
 For Each oControl In Me.Controls
  If oControl.Name Like "TextBox*" Then
   x = Right(oControl.Name, Len(oControl.Name) - 7) 'TextBox123
                                                    '1     7  10=Len()
   oControl.Value = ThisWorkbook.Sheets(1).Cells(x, y)
  End If
 Next
End Sub

score 0 · Accepted Answer · answered Dec 01 '14 at 07:53

If you have a fixed number of Textboxes, you can use the below code:

Private Sub UserForm_Activate()
    Dim i As Integer
    Dim j As Integer
    j = 1             'set it to the required column number
    For i = 1 To 500  'set 500 to the maximum no. of textboxes
        Controls("TextBox" & i).Value = ThisWorkbook.Sheets("Sheet1").Cells(i, j)
    Next i
End Sub

score 0 · Answer 3 · answered Dec 02 '14 at 06:21

Here is the code i modified for my needs. Thanks Rusk and Axel. Axel's answer has a lot more depth to it. I am sure I will use it as a basis later. Fumu's answer also looks deep. Thank you all.

Private Sub UserForm_Activate()

Dim i As Integer
Dim j As Integer
Dim k As Integer



j = 138             'set it to the required column number
k = 19
For i = 1 To 187  'set 500 to the maximum no. of textboxes

        If j = 149 Then
        j = 138 'this resets the column number to beginning
            If k = 30 Then k = 37 'this skips the gap in the table
        k = k + 1 'this incements the row number to the next row
        End If

    Controls("TextBox" & i).Value = Sheet9.Cells(k, j)
    j = j + 1 'this increment column number to next



Next i


End Sub

score -1 · Answer 4 · answered Dec 01 '14 at 07:39

You can access each textbox as a member of worksheet.shapes.item .

Following code may assigne the values of cells(x,y),cells(x+1,y)... to textboxes in the worksheet.

Sub SetValueToTextboxes(x,y)
　Dim i As Integer, dim j as Integer

　With ActiveSheet.Shapes
    j=0
    For i = .Count To 1 Step -1
　　　If .Item(i).Type = msoTextBox Then 'set data if the shape is 'textbox'
    　　.Item(i).TextFrame.Characters.Text = .cells(x+j,y).value
       j=j+1
　    End If
    Next i
  End With
End Sub

I hope you can get some idea about to handle textboxes.

VBA Filling up 500+ textboxes

4 Answers4