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.
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.
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)
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
supergoat: Check out this post
http://www.dicks-blog.com/archives/2004/06/29/preserving-listbox-selections/
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?
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.
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
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
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.