Copying only one column of Userform ListBox data to single cell in separate spreadsheet with commas separating data

Question

I made a multicolumn ListBox with two columns where I can search for a customer name and have the results display customer part names in column 1 and associated component numbers in column 2. I want to be able to extract the entirety of column 2 (of part numbers) into a specific cell, J9, in my workbook's "New Profile Part Template" tab once a user finds a customer name.

Below is my code. I've looked through a lot of possibilities online, but I can't seem to locate any code that does what I want it to.

Below is a list of the current code; The data from ListBox Columns 1 and 2 are supposed to be pasted into cell J9 with commas separating the values, but instead, CommandButton1 pastes all of the data into cell J9:

Option Explicit
' Display All Matches from Search in Userform ListBox
Dim FormEvents As Boolean

Private Sub ClearForm(Except As String)

' Clears the list box and text boxes EXCEPT the text box
' currently having data entered into it

Select Case Except

    Case "FName"
        FormEvents = False
        LName.Value = ""
        Results.Clear
        FormEvents = True

    Case "LName"
        FormEvents = False
        FName.Value = ""
        Results.Clear
        FormEvents = True
        
    Case Else
        FormEvents = False
        FName.Value = ""
        LName.Value = ""
        Results.Clear
        FormEvents = True
        
    End Select

End Sub

Private Sub ClearBtn_Click()

ClearForm ("")

End Sub

Private Sub CloseBtn_Click()

Me.Hide

End Sub

Private Sub FName_Change()

    If FormEvents Then ClearForm ("FName")

End Sub

Private Sub LName_Change()

    If FormEvents Then ClearForm ("LName")

End Sub


Private Sub Results_Click()

End Sub

Private Sub SearchBtn_Click()
    Dim SearchTerm As String
    Dim SearchColumn As String
    Dim RecordRange As Range
    Dim FirstAddress As String
    Dim FirstCell As Range
    Dim RowCount As Integer

    ' Display an error if no search term is entered
    If FName.Value = "" And LName.Value = "" Then
        MsgBox "No search term specified", vbCritical + vbOKOnly
        Exit Sub
    End If

    ' Work out what is being searched for
    If FName.Value <> "" Then
        SearchTerm = FName.Value
        SearchColumn = "Service Part"
    End If

    If LName.Value <> "" Then
        SearchTerm = LName.Value
        SearchColumn = "Part Number"
    End If

    Results.Clear

        ' Only search in the relevant table column i.e. if somone is searching Service Part Name
        ' only search in the Service Part column
        With Sheet3.Range("Table1[" & SearchColumn & "]")

            ' Find the first match
            Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)

            ' If a match has been found
            If Not RecordRange Is Nothing Then

            FirstAddress = RecordRange.Address
            RowCount = 0

            Do
            
                ' Set the first cell in the row of the matching value
                Set FirstCell = Sheet3.Range("A" & RecordRange.Row)
                
                ' Add matching record to List Box
                Results.AddItem
                Results.List(RowCount, 0) = FirstCell(1, 1)
                Results.List(RowCount, 1) = FirstCell(1, 2)
                
                RowCount = RowCount + 1
                
                ' Look for next match
                Set RecordRange = .FindNext(RecordRange)

                ' When no further matches are found, exit the sub
                If RecordRange Is Nothing Then
                    Exit Sub
                End If

            ' Keep looking while unique matches are found
            Loop While RecordRange.Address <> FirstAddress

        Else
        
            ' If you get here, no matches were found
            Results.AddItem
            Results.List(RowCount, 0) = "Nothing Found"
        
        End If
        
    End With
End Sub

Private Sub CommandButton1_Click()
   Dim i 'to store the item of the list
   Dim j 'just a counter
   Dim sht As Worksheet
    Set sht = Sheets("New Profile Part Template")
    j = 0 'Initiate the counter
   For Each i In Me.Results.List
    j = j + 1 'add one to the counter
    sht.Cells(9, 10).Value = sht.Cells(9, 10).Value & Chr(10) & i

    Next i
End Sub


Private Sub UserForm_Initialize()

    FormEvents = True

End Sub

The CommandButton1 near the end of the code is causing me problems; the Userform and multicolumn listbox function flawlessly. Only column 2 of the ListBox, titled "Results," needs to be extracted to cell J9. Preferably, the numbers should be separated by a space or comma.

Answer 1

Try this:

Private Sub CommandButton1_Click()

    Dim sht As Worksheet
    Set sht = Sheets("New Profile Part Template")

    Dim data As Variant
    data = Application.Index(Me.Results.List, 0, 2)
    
    sht.Cells(9, 10).Value = Application.TextJoin(", ", True, data)
    
End Sub