Populate TextBox's in VBA

Question

I am using a form to show outstanding work. the code creates a number of textboxes based on the number of rows used on a sheet call "Jobcardslive"

I can get it to create the right number of textboxes on the form but i would also like to populate the textboxes with a value stored in Row A

e.g If I have 4 rows populated on the sheet it will create 4 textboxes named vehicle1 - 4 etc I would also like it to populate vehicle1 with A1 from the sheet and vehicle2 with A2 etc

The boxes are created fine the code i am using at the moment is

Dim txtB1 As Control
Dim TextBox_Name As String
Dim f As String
f = ThisWorkbook.Sheets("Jobcardslive").Range("A" & Rows.Count).End(xlUp).Row - 1
Dim i

For i = 0 To f
    Set txtB1 = Controls.Add("Forms.TextBox.1")
    With txtB1
        .Name = "vehicle" & i
        .Height = 20
        .Width = 200
        .Left = 10
        .Top = 10 * i * 2
    End With
Next i

Any help would be greatly appreciated

... [but better don't](http://stackoverflow.com/a/39273723/11683). — GSerg, Sep 10 '16 at 12:37

score 1 · Accepted Answer · answered Sep 10 '16 at 12:33

1

you could go like follows:

    Dim txtB1 As MSForms.TextBox '<--| declare it as a TextBox instead of Control, to have Intellisense show you real `Textbox` object specific members
    Dim i As Long

    With ThisWorkbook.Sheets("Jobcardslive")
        For i = 1 To .Range("A" & .Rows.Count).End(xlUp).Row
            Set txtB1 = Me.Controls.Add("Forms.TextBox.1")
            SetTextBox txtB1, i, Range("A" & i).value
        Next i
    End With

thus also demanding to the following specific Sub SetTextBox the task of properly initializing the textbox:

Sub SetTextBox(txtB As MSForms.TextBox, i As Long, v As Variant)
    With txtB
        .name = "vehicle" & i
        .height = 20
        .Width = 200
        .Left = 10
        .Top = 10 * i * 2
        .value = v
    End With
End Sub

answered Sep 10 '16 at 12:33

user3598756

28,893
4
18
28

If i wanted to move the textboxes to the right when the end of the form is reached could i do this? – David Pearce Sep 10 '16 at 13:05
_"To mark an answer as accepted, click on the check mark beside the answer to toggle it from greyed out to filled in"_ – user3598756 Sep 10 '16 at 13:29
As for the textbox moving relative to userfom borders, this is a new issue and as per this site rules you must post a new question, possibly with your code attempts at solving it – user3598756 Sep 10 '16 at 13:38

Populate TextBox's in VBA

1 Answers1