Userforms - multi listbox user selections - cannot force new line in same cell

Hi, this is my first post and I hope I have followed the rules as directed correctly.

I have searched many forums for developing my VBA Userform, and as a beginner, I have created my userform through test samples and other peoples guidance/code as I came across error/problems.
As a result,pieced bits together I have now created something which works, has some dynamic coding, with drop down choices interactive sheet pre-selections from prior user selections navigating the user automatically to the next required page based on selection (all pages are hidden to the user upon initialise).

• My userform is multipage, populates listbox and combobox via "Additem" using a variety of cases/index within the VBA coding. Nothing is populated from the excel sheet.
• The userform is actioned via a click event on the sheet to action the UserForm_Initialize() sub for user entries.
• The populated excel sheet "Sheet1" for example, works as I want, populating the sheet from framed option buttons, text boxes, dates and auto unique ID for each new row created by userform as each user enters data, all good and very pleased.

Column H, is where the ListBox1 populates the user selected choices. The Listbox1 is populated via two ComboBoxes. The First ComboBox selection dictates the contents of the second ComboBox. The second ComboBox user selection, dictates the ListBox1 contents for the User to select one or more items. (fmMultiSelectMulti)

• As an example, next available clear row is populated with the userform details, so if row 2 is already filled with data, row 3 will be next, with each column receiving specific userform data i.e Col A = ID#, Col B = Name, Col C = Project Number etc. Col H received the ListBox1 user selected strings. these populate the next available cell in Col H with a comma separated string(s). these range from a single predefined selection choice upto 10 choices.

In Col H, (assuming the user selects four of the choices available from the list), the cell contents after click control (OK) this looks like:

Text String Selected No 1,Text String Selected No 2,Text String Selected No 3,Text String Selected No 4,etc

I want to have:

Text String Selected No 1
Text String Selected No 2
Text String Selected No 3
Text String Selected No 4
etc

I have tried using the split function without success, which is no doubt my not understanding all the coding language, so my question is how can I achieve a forced line in the cell for each user selection chosen when "printing" to the sheet?

I have pasted the excerpt of code below which I am having difficulty.

Private Sub OKButton_Click()




Dim emptyRow As Long




'Make Sheet1 active
Sheet1.Activate




ActiveSheet.Unprotect "password"




'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1








'Transfer information
Cells(emptyRow, 1).Value = IDTextBox.Value
Cells(emptyRow, 3).Value = ProjectNumberTextBox.Value
Cells(emptyRow, 4).Value = CompletedByTextBox.Value
Cells(emptyRow, 5).Value = DateTextBox.Value


'Transfers selected items to Col H
'The Flg is a TRUE/FALSE variable which becomes TRUE when there is one or more item selected in Listbox.




Dim i   As Long, txt As String, Flg As Boolean
 
With Me.ListBox1
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            Flg = True
            txt = txt & "," & .List(i)
        End If
    Next
End With
If Flg Then
    With Sheets("Design Checklist and QAQC")
        .Range("H" & .Rows.Count).End(xlUp).Offset(1).Value = Mid$(txt, 2)
    End With


'the above code works fine and populates into Col H next available row and correct cell, but is not split into forced lines


'new code (2) to split text in active cell - trying to integrate it with above working code


Dim strInput As String, strOutput As String
Dim varZz As Variant, j As Integer ' change from i to j as error with duplicate i
     
    strInput = Active.Cell.Value ' tried changing from activecell.Value to Flg to txt none worked.
    varZz = Split(strInput, ",") 'thought this would help split my txt
    For j = LBound(varZz) To UBound(varZz)
        strOutput = varZz(j)
    Next j


'end of new code (2)


End If


If 


'..... rest of code for option buttons etc., then locking sheet and end.

I hope the above is no too truncated and look forward to some useful pointers, correction, guidance to reach my requirements successfully. Thanks

Re: Userforms - multi listbox user selections - cannot force new line in same cell

Never too simplistic - thank you so much Cytop, works like a dream and tested several times now ! Goes to show how much I still need to learn !

The basis of the below was from another moderator on Ozgrid, so thanks to Krishnakumar also.

Final Code to help others learning like myself perhaps?

Dim i   As Long, txt As String, Flg As Boolean
 
With Me.ListBox1
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            Flg = True
            'txt = txt & "," & .List(i)
            txt = txt & Chr(10) & .List(i)
        End If
    Next
End With
If Flg Then
    With Sheets("Sheet1") 'change to your own sheet name
        .Range("H" & .Rows.Count).End(xlUp).Offset(1).Value = Mid$(txt, 2) ' change "H" to required Column
    End With
End If

Would you be kind enough to briefly explain why the '& "," & does not work yet the & Chr(10) & does ?

I was led to believe placing a character between quotes ( or text) would allow VBA to pickup the quoted element, being an actual "item" within the userform, similar to specifying the actual sheet name as opposed to "Sheet1" for example. All the split function I came across use the " find the delimiter".

Also, how to I uprate / score you please to say a big thank you on the forum ?

Re: Userforms - multi listbox user selections - cannot force new line in same cell

You've been a great help and I appreciate your time in helping me to conclude my problem successfully.

The explanation and coding above I will take a more detailed look at and understand how it works.

Once again many thanks.