excel - Get values from a listbox on a sheet

Question

I have a listbox named ListBox1 on Sheet1 of an Excel workbook.

Every time the user selects one of the items in the list, I need to copy its name to a variable named strLB.

So, if I have Value1, Value2, Value3, Value4 and the user selects Value1 and Value3, I need my strLB to come out as Value1,Value3.

I tried doing that post hoc with:

For i = 1 To ActiveSheet.ListBoxes("ListBox1").ListCount
    If ActiveSheet.ListBoxes("ListBox1").Selected(i) Then strLB = strLB & etc.etc.
Next i

But this is very slow (I have 15k values in my listbox). This is why I need to record the selection in real time and not in a cycle, after the user is done inputting.

I'm going to also need a way to check if the user removed any of the previous selection.

Answer 1

Unfortunately for MSForms list box looping through the list items and checking their Selected property is the only way. However, here is an alternative. I am storing/removing the selected item in a variable, you can do this in some remote cell and keep track of it :)

Dim StrSelection As String

Private Sub ListBox1_Change()
    If ListBox1.Selected(ListBox1.ListIndex) Then
        If StrSelection = "" Then
            StrSelection = ListBox1.List(ListBox1.ListIndex)
        Else
            StrSelection = StrSelection & "," & ListBox1.List(ListBox1.ListIndex)
        End If
    Else
        StrSelection = Replace(StrSelection, "," & ListBox1.List(ListBox1.ListIndex), "")
    End If
End Sub