VBA : Transfer Data from VBA Form to Excel worksheet

[KMGIL]

Hi
I have created a VBA excel form which pre populates a number of text boxes based on an excel file . As well as the Textboxes there are a number of comboboxes. The data that is generated from the excel file is based on a user selecting an email address so the number of rows changes each time.

The form works for this .

What I do not know is how I can transfer this data from the form to an excel worksheet so that the various number of rows are always captured.

Please see below code to date for form generation


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 = 15
.Height = 15
.Top = i * 22
.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 = 15
.Top = i * 22
.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.textbox.1")
With tbox
.Name = i
.Left = 160
.Height = 15
.Top = i * 22
.Width = 250
.Font.Size = 8
.Locked = True
.Value = Sheet2.Cells(i, 7)
.BackColor = &H80&
.BorderColor = &H80&
.ForeColor = &HFFFFFF


End With




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


End With




'Line 5
Set tbox = Controls.Add("Forms.textbox.1")
With tbox
.Name = i
.Left = 750
.Height = 15
.Top = i * 22
.Width = 50
.Font.Size = 8
.Locked = True
.Value = Format(Sheet2.Cells(i, 10), "#####.00")
.BackColor = &H80&
.BorderColor = &H80&
.ForeColor = &HFFFFFF


End With


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








End With




'Line 7
Set tbox = Controls.Add("Forms.ComboBox.1")
With tbox
.List = Array("< 1 Month", "1-2 Months", "2-3 Months", "3 Months +")
.Name = i
.Left = 880
.Height = 15
.Top = i * 22
.Width = 70
.Font.Size = 8
.BackColor = &H80&
.BorderColor = &H80&
.ForeColor = &HFFFFFF




End With


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






End With




'Line 9
Set tbox = Controls.Add("Forms.textbox.1")
With tbox
.Name = i
.Left = 1030
.Height = 15
.Top = i * 22
.Width = 250
.Font.Size = 8
.Locked = False
.Value = Sheet2.Cells(i, 15)
.BackColor = &H80&
.BorderColor = &H80&
.ForeColor = &HFFFFFF


End With






' Userform height Setting
If i <= 2 Then
Me.Height = i * 80
ElseIf i <= 10 Then
Me.Height = i * 40
Else
Me.Height = i * 25
End If


Next i




End Sub