Help Needed for user form command button

[freeisgood]

Hello All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Codes below is for a command button of a user form (codes found on the web), for each execution of this command, items selected by users is separated by a comma. My goal is to have them separate by a break line. I added “char(10)” to line 6 "strItems = strItems & "," & Chr(10) & ListBox1.List(lngItem)". It works, with the exception that it start by a break line then entry selected by users. <o></o>
<o></o>
I am seeking help to make the necessary adjustment to remove the unnecessary break line (first one only). I am learning VBA and a quick explanation would be greatly appreciated.<o></o>
<o></o>
Code:<o></o>
Private Sub CommandButton1_Click()<o></o>
<o></o>
Dim lngItem As Long, strItems<o></o>
<o></o>
With ListBox1<o></o>
For lngItem = 0 To ListBox1.ListCount - 1<o></o>
If ListBox1.Selected(lngItem) Then<o></o>
strItems = strItems & "," & Chr(10) & ListBox1.List(lngItem) – 1<o></o>
<o></o>
End If<o></o>
Next lngItem<o></o>
End With<o></o>
<o></o>
ActiveCell.Value = Replace(strItems, ",", "", 1, 1)<o></o>
<o></o>
Unload Me<o></o>
<o></o>
End Sub<o></o>
<o></o>
Regards<o></o>
VL<o></o>

 

[mikerickson]

Try this

Private Sub CommandButton1_Click()
    Dim lngItem As Long, strItems As String

    strItems = ""

    With ListBox1
        For lngItem = 0 To ListBox1.ListCount - 1   
            strItems & Chr(10) & ListBox1.List(lngItem) – 1
        Next lngItem
    End With
    
    strItems = Mid(strItems, 2): Rem remove first chr(10)

    ActiveCell.Value = strItems

    Unload Me

End Sub

 

[repairman615]

Hi Mike,

That was more of the results I was expecting.

Needed a small adj.:

strItems = strItems & Chr(10) & ListBox1.List(lngItem)

-Jeff

 

[mikerickson]

Oops. Thanks for the catch.

 

[freeisgood]

What did I do wrong? Even with suggested adjustment by Repairman615 still does not work.

Codes:

Private Sub CommandButton1_Click()
Dim lngItem As Long, strItems As String
strItems = ""
With ListBox1
For lngItem = 0 To ListBox1.ListCount - 1
strItems = strItems & Chr(10) & ListBox1.List(lngItem)
Next lngItem
End With
strItems = Mid(strItems, 2): Rem remove first chr(10)
ActiveCell.Value = strItems
Unload Me
End Sub

 

[mikerickson]

What "doesn't work"?
Does nothing go into the cell? Or does the wrong cell go into the cell? or

Perhaps

Private Sub CommandButton1_Click()
    Dim lngItem As Long, strItems As String
    strItems = ""
    With ListBox1
        For lngItem = 0 To .ListCount - 1
            If .Selected(lngItem) Then strItems = strItems & Chr(10) & ListBox1.List(lngItem)
        Next lngItem
    End With

    strItems = Mid(strItems, 2): Rem remove first chr(10)
    ActiveCell.Value = strItems

    Unload Me
End Sub

 

[Norie]

You could try an array, something like this perhaps.

Option Explicit
 
Private Sub CommandButton1_Click()
Dim lngItem As Long, strItems As String
Dim arrItems()

    With ListBox1
        For lngItem = 0 To ListBox1.ListCount - 1
            ReDim Preserve arrItems(lngItem)
            arrItems(lngItem) = ListBox1.List(lngItem)
        Next lngItem
    End With

    strItems = Join(arrItems, Chr(10))
 
    ActiveCell.Value = strItems

    Unload Me

End Sub

 

[freeisgood]

Thanks Mikerickson,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I misunderstood Repairman615’s instruction, now it works.<o></o>
<o> </o>
Again thanks all!<o></o>