Multiple Records (Rows) from 1 Text Box, Userform

[bugelboy93]

I have a userform for data entry which populates a worksheet:

txtTrackingNumber
txtDate
txtName (should this even be a text box?)

I want Name to be able to accept one or more values and give me as many rows as there are values in Name, with the Tracking Number and Date being duplicated in each row.

e.g. what I enter:

Tracking Number - 08-0001
Date - 6/10/08
Name(s): Smith, Jones, Brown

What I get in my Excel sheet:

Tracking Number <<tab>>Date<<tab>>Name
08-0001 <<tab>> 6/10/08 <<tab>> Smith
08-0001 <<tab>> 6/10/08 <<tab>> Jones
08-0001 <<tab>> 6/10/08 <<tab>> Brown

Thanks in advance

 

[iggydarsa]

Assuming your columns span from Column A to Column C, you can use something like this:

Private Sub CommandButton1_Click()
Dim r As Long: r = Range("A" & Rows.Count).End(xlUp).Row + 1
Dim vName As String

For i = 1 To Len(txtName.Text)
    If Not Mid(txtName.Text, i, 1) = "," Then
        vName = vName & Mid(txtName.Text, i, 1)
    Else
        Range("A" & r).Value = txtTrackingNumber.Text
        Range("B" & r).Value = txtDate.Text
        Range("C" & r).Value = Trim(vName)
        r = r + 1
        vName = ""
    End If
Next i
Range("A" & r).Value = txtTrackingNumber.Text
Range("B" & r).Value = txtDate.Text
Range("C" & r).Value = Trim(vName)
r = r + 1
vName = ""
Unload Me
End Sub