Daily Dose of Excel

Listboxes from the Control Toolbox (also called ActiveX Listboxes) have a property called MultiSelect. This property allows the user to select more than one item in the listbox. Valid settings for this property are:

0 - fmMultiSelectSingle: User can only select one item at a time.
1 - fmMultiSelectMulti: User can click or press the spacebar to select and deselect items.
2 - fmMultiSelectExtended: User can use SHIFT and CTRL to select multiple items.

To process selected items in a MultiSelect listbox, you need to cycle through the items and use the Selected property of the ListBox object. An example:

Sub ShowSelected()
    Dim i As Long
    Dim Msg As String
    
    With Sheet1.ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                Msg = Msg & .List(i) & vbCrLf
            End If
        Next i
    End With
    
    If Len(Msg) = 0 Then
        Msg = “No items selected”
    End If
    
    MsgBox Msg
End Sub

I put most of this sub in a With Block. It speeds up the code and helps readability. The For Next loop loops through all the items in the listbox. The first item is numbered 0 and the last item is numbered ListCount-1. The Selected property is an array with the same number of elements as there are items in the listbox. It contains either True, if that particular item is selected, or False, if not.

If Selected(i) is True, then the item is concatenated with the variable Msg (and a new line) to be shown later. If it’s False, nothing happens and the loop goes to the next item. The last If Then tests Msg to see if anything was selected and the MsgBox function is used to display the selected items or another message if nothing was selected.

The item that is concatenated to the variable is accessed with the List property. Like Selected, List is an array that has the same number of elements as there are items in the listbox. Unlike Selected, List contains the value of the items. Incidentally, List also has a column argument. The example above assumes a single column listbox, so the column argument isn’t used.