Need code to list out all selected items from a listbox

[atf32]

Looking for code that will list out all the selected items from a listbox and place them in a range on the surface of excel. So if the listbox lists 10 different fruits and the user selects 3 of them, then only the 3 selected fruits will be listed in cells "A1", "A2" and "A3". Then everytime the user selects or deselects and item, the displayed list is updated.

 

[VBA Geek]

is your listbox on a USERFORM or on the spreadsheet

 

[atf32]

On the spreadsheet.

 

[VBA Geek]

add this in your worksheet module, please note the name in bold of the listbox which probably you will need to change

Const ExportCell As String = "A1" ' <-- where your selection is printed


Private Sub myListBox_Change()
Dim Ndx As Long, ASelection As Variant
With Me
    .Columns("A").ClearContents
    With .[SIZE=3][FONT=arial black][B]myListBox[/B][/FONT][/SIZE]
        ReDim ASelection(0 To .ListCount - 1)
        For Ndx = 0 To .ListCount - 1
            If .Selected(Ndx) Then
                ASelection(Ndx) = .List(Ndx)
            Else
                ASelection(Ndx) = Chr$(11)
            End If
        Next
    End With
    ASelection = Filter(ASelection, Chr$(11), False, vbBinaryCompare)
    If Not UBound(ASelection) = -1 Then .Range(ExportCell).Resize(UBound(ASelection) + 1).Value = Application.Transpose(ASelection)
End With
End Sub

On the spreadsheet.

 

[atf32]

Awesome that worked exactly how I wanted it to.
Also, thank you for the quick response.

 

[VBA Geek]

no problem, thx for feedback

Awesome that worked exactly how I wanted it to.
Also, thank you for the quick response.