VBA to get values from a listbox on a spreadsheet

问题:

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

Everytime 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. Pretty straightforward.

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 actually 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.

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

Hope you guys can help!!

回答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

回答2:

The accepted answer doesn't cut it because if a user de-selects a row the list is not updated accordingly.

Here is what I suggest instead:

Private Sub CommandButton2_Click()

Dim lItem As Long

    For lItem = 0 To ListBox1.ListCount - 1

        If ListBox1.Selected(lItem) = True Then

            MsgBox(ListBox1.List(lItem))

        End If

    Next

End Sub

Courtesy of http://www.ozgrid.com/VBA/multi-select-listbox.htm

回答3:

To get the value of the selected item of a listbox then use the following.

For Single Column ListBox: ListBox1.List(ListBox1.ListIndex)

For Multi Column ListBox: ListBox1.Column(column_number, ListBox1.ListIndex)

This avoids looping and is extremely more efficient.

回答4:

Take selected value:

worksheet name = ordls
form control list box name = DEPDB1

selectvalue = ordls.Shapes("DEPDB1").ControlFormat.List(ordls.Shapes("DEPDB1").ControlFormat.Value)