Multi select ListBox

[mdo8105]

Hi,

I was able to return multiple values into one cell; however, I am trying to figure out how to change my delimiter from"," to Char(10):

 For i = 0 To DEPListBox.ListCount - 1            If DEPListBox.Selected(i) Then     'If selected is True
                strTemp = strTemp & DEPListBox.List(i) & ","      'Edit ", " to required separators
            End If
        Next i
        
        If Len(strTemp) > 2 Then    'The variable will always be greater than 2 if any selections in the listbox
            strTemp = Left(strTemp, Len(strTemp) - 2) 'remove last comma and space (Edit 2 to length of separators)
        End If
        
ThisWorkbook.Worksheets("TST_SHEET").Cells(dcc + 1, 10).Value = strTemp

Thank you

 

[Yongle]

Replace a comma with char 10 in variable myString like this:

myString = Replace(myString, ",", Chr(10))

 

[Rick Rothstein]

Hi,

I was able to return multiple values into one cell; however, I am trying to figure out how to change my delimiter from"," to Char(10):

 For i = 0 To DEPListBox.ListCount - 1            If DEPListBox.Selected(i) Then     'If selected is True
                strTemp = strTemp & DEPListBox.List(i) [B][COLOR="#FF0000"]& vbLf[/COLOR][/B]      'Edit ", " to required separators
            End If
        Next i
        
        If Len(strTemp) > 2 Then    'The variable will always be greater than 2 if any selections in the listbox
            strTemp = Left(strTemp, Len(strTemp) - [B][COLOR="#FF0000"]1[/COLOR][/B]) 'remove last comma and space (Edit 2 to length of separators)
        End If
        
ThisWorkbook.Worksheets("TST_SHEET").Cells(dcc + 1, 10).Value = strTemp

See the changes in red above... note that vbLf is a built-in constant for the Line Feed character which is also Chr(10) in VBA, not CHAR(10) as that is an Excel worksheet function name.

 

[mdo8105]

Thank you both so much. I ended up using Rick's concept and it worked beautifully.