VBA Form : Add Combobox to Form when number of rows will vary

[KMGIL]

Hi

I have created a user form with a number of textboxes . The Textboxes are pre populated based on an excel data base

I would like to add a Combobox with properties of "Yes" or "No" in the drop down list.

The number of rows is dependent on an end user selecting their email address and clicking on a command button which opens up the form.

Example if end user selects ab@abTest.com then 5 rows of data (text boxes ) will appear. if end users selects bc@abtest.com then 10 rows will appear etc

How do i get a combobox to appear in each row as well with the option of "Yes" or "No"

My Code is per below

Thanks

kind Regards

Ken


Private Sub CommandButton1_Click()
Dim i As Long
For i = 1 To Sheet2.Range("A1000000").End(xlUp).Row


'Line 1
Set tbox = Controls.Add("Forms.textbox.1")
With tbox
.Name = i
.Left = 10
.Height = 20
.Top = i * 20
.Width = 75
.Font.Size = 8
.Locked = True
.Value = Sheet2.Cells(i, 3)
.BackColor = &H80&
.BorderColor = &H80&
.ForeColor = &HFFFFFF


End With








'Line 2
Set tbox = Controls.Add("Forms.textbox.1")
With tbox
.Name = i
.Left = 100
.Height = 20
.Top = i * 20
.Width = 40
.Font.Size = 8
.Locked = True
.Value = Sheet2.Cells(i, 4)
.BackColor = &H80&
.BorderColor = &H80&
.ForeColor = &HFFFFFF


End With

'Line 3
Set tbox = Controls.Add("Forms.Combobox1.1")
With tbox
.Name = i
.Left = 820
.Height = 20
.Top = i * 20
.Width = 20
.Font.Size = 8
.Locked = False
.BackColor = &H80&
.BorderColor = &H80&
.ForeColor = &HFFFFFF

 

[KMGIL]

Hi Solved problem

new code below works

thanks ken

'Line 6
Set tbox = Controls.Add("Forms.ComboBox.1")
With tbox
.List = Array("Yes", "No")
.Name = i
.Left = 820
.Height = 20
.Top = i * 20
.Width = 40
.Font.Size = 8
.BackColor = &H80&
.BorderColor = &H80&
.ForeColor = &HFFFFFF