CODEWITHSUNDEEP
vbaexcel
David Pearce
David Pearce

Reputation: 13

Populate TextBox's in VBA

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

Upvotes: 1

Views: 1881

Answers (1)

user3598756
user3598756

Reputation: 29421

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

Upvotes: 1

Related Questions