Get values from a listbox on a sheet

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

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.

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