VBA outputting all listbox entries to a single cell-CodePudding

I currently have a form whereby a person inputs a sales order number and a part number into a form and submits that information to a sheet when the submit button is clicked. What I am trying to do and currently failing is having the ability to input a single sales order number but have up to 20 part numbers associated to that single sales order number.

I am trying to do this with a listbox whereby the user inputs the part number info into form fields, clicks add and then that adds all of that info to a listbox (that part is working). However when I click submit on the main form the sheet is only populated with the first entry in the listbox. This is the code I have to input the information into the listbox,

Private Sub Add_Part_Click()

PartInfo.AddItem "PartNo:" & PartNo & Space(10) & "Part Quantity:" & PartQnt & Space(10) & "Part Description:" & PartDesc
PartNo.Value = ""
PartQnt.Value = ""
PartDesc.Value = ""

End Sub

Below is the code I am using to then populate the sheet with the form information

    Private Sub Sales_Submit_Click()
    
    If SalesOrderNo = "" Then
    MsgBox "Missing Sales Order Number, Please ensure one is added before attempting to continue", vbCritical, "Missing Sales Order Number"
    Exit Sub
    End If
    
    If Len(SalesOrderNo) <> 7 Then
    MsgBox "Sales Order Number too short, please try again", vbOKOnly, "Sales Order Number Incorrect"
    Exit Sub
    End If
    
    Dim TargetRow As Integer
    
    'Sets the default position to add data from
    TargetRow = Sheets("Backend").Range("O9").Value   1
    
    'Populates the form data into relevant columns
    Sheets("Sales Order Log").Range("Sales_Data_Start").Offset(TargetRow, 1).Value = SalesOrderNo
    Sheets("Sales Order Log").Range("Sales_Data_Start").Offset(TargetRow, 2).Value = Customer
    Sheets("Sales Order Log").Range("Sales_Data_Start").Offset(TargetRow, 3).Value = SiteName
    Sheets("Sales Order Log").Range("Sales_Data_Start").Offset(TargetRow, 4).Value = GMNo
    Sheets("Sales Order Log").Range("Sales_Data_Start").Offset(TargetRow, 5).Value = PartInfo.list
    Sheets("Sales Order Log").Range("Sales_Data_Start").Offset(TargetRow, 6).Value = SalesDate
End Sub

I want to try and have the functionality whereby the listbox contents are all submitted into a single cell on the sheet.

Thank you

CodePudding user response：

You will need to loop over all entries in the list manually. Advice: Use a With-Statement to make your code more readable.

Dim parts As String
For i = LBound(Me.ListBox1.List) To UBound(Me.PartInfo.List)
    parts = parts & IIf(parts = "", "", ", ") & Me.PartInfo.List(i, 0)
Next

With Sheets("Sales Order Log").Range("Sales_Data_Start")
    .Offset(TargetRow, 1).Value = SalesOrderNo
    ' (...)
    .Offset(TargetRow, 5).Value = parts
End With