Using Multiselect Listboxes

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.

8 thoughts on “Using Multiselect Listboxes

  1. I notice that when you use MultiSelect Extended or Multi, Excel wipes out your selections. I link the list to an Excel range, select some items, Save, open the workbook again and nothing is selected. Please help if you know a workaround.

  2. FIGURED IT OUT!!!

    You use Shape.OLEFormat.Object to get to the original ActiveX object from the Excel control. From then on, it acts as a normal ActiveX control. (use ListBox.Selected(index) = False/True)

  3. Eugene/Dick,

    Can you explain a litter further how i ensure that items selected in a multi-select listbox are saved and retrieved when the worksheet is reopened.

    Thanks

  4. I’ve a ListBox with the setting of MultiSelectExtended. I need to point the selected data to a series of cells in another spreadsheet for my further action. So far, I am only able to link to 1 cell but it’s not what I wanted. Can you help?

  5. Can you make a listbox = all files in a folder, so if you add or delete files they will be added or removed from the listbox? I could make a static list box for the values (filenames), but I would rather have it read them without needing to update.

  6. I am afraid that I do not understand Macros a lot, but I have copied the “Same Cell” macro from http://www.contextures.com and modified the columns to suit my needs which is excellent (listed below). Where does the above go within this macro.
    I would probably need #1.
    Also does this remove the problem of when you go back to the cell to either add another item from the list or delete one where it adds to the cell the original contents plus the amended ones.
    Thanks
    Greg

    Private Sub Worksheet_Change(ByVal Target As Range)

        Dim rngDV As Range
        Dim oldVal As String
        Dim newVal As String

        If Target.Count > 1 Then GoTo exitHandler
       
        On Error Resume Next
            Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
        On Error GoTo exitHandler
       
        If rngDV Is Nothing Then GoTo exitHandler
       
        If Intersect(Target, rngDV) Is Nothing Then
           ‘do nothing
       Else
          Application.EnableEvents = False
          newVal = Target.Value
          Application.Undo
          oldVal = Target.Value
          Target.Value = newVal
          If (Target.Column = 13) Or (Target.Column = 17) Or (Target.Column = 21) Or (Target.Column = 24) Or (Target.Column = 49) Or (Target.Column = 50) Or (Target.Column = 51) Or (Target.Column = 52) Or (Target.Column = 53) Or (Target.Column = 54) Or (Target.Column = 55) Or (Target.Column = 56) Or (Target.Column = 57) Or (Target.Column = 58) Or (Target.Column = 59) Or (Target.Column = 60) Or (Target.Column = 61) Then
            If oldVal = “” Then
              ‘do nothing
             Else
              If newVal = “” Then
              ‘do nothing
             Else
              Target.Value = oldVal _
                & “, “ & newVal
              End If
            End If
          End If
        End If
       
    exitHandler
          Application.EnableEvents = True
    End Sub

  7. Greg: The code in this post relates to ActiveX controls while your code relates to data validation. They are different. I don’t think there is any way to select more than one entry in data validation.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.