I made an example regarding how to sort listbox with multi select options.
I use 'System.Collections.ArrayList' object to populate the list because this object has 'built in' sort function.
This is how it work:
1. user pick an item in combobox. It will sort the listbox and clear selection.
2. user pick some item in listbox
3. hit the command button
4. the selected item will be on top & the list below it is sorted.
Private Sub ComboBox1_Change()
Dim dar As Object, r As Range
Set dar = CreateObject("System.Collections.ArrayList")
For Each r In Range("A2", Cells(Rows.Count, "A").End(xlUp))
dar.Add r.Value
Next
dar.Sort
ListBox1.List = dar.toarray()
End Sub
Private Sub CommandButton1_Click()
Dim dar As Object, i As Long
Set dar = CreateObject("System.Collections.ArrayList")
With ListBox1
'get selected item to dar
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
dar.Add .List(i)
.List(i) = 0 'selected item become 0
End If
Next i
'get listbox list to dar1 and the sorted
Set dar1 = CreateObject("System.Collections.ArrayList")
For i = 0 To .ListCount - 1
dar1.Add .List(i)
Next i
dar1.Sort
ListBox1.List = dar1.toarray()
'get dar value back to selected item
dar.Sort
For i = 0 To dar.Count - 1
.List(i) = dar.toarray()(i)
.Selected(i) = True
Next
End With
End Sub
Private Sub UserForm_Initialize()
Dim dar As Object, r As Range
ListBox1.MultiSelect = fmMultiSelectMulti
Set dar = CreateObject("System.Collections.ArrayList")
For Each r In Range("A2", Cells(Rows.Count, "A").End(xlUp))
dar.Add r.Value
Next
dar.Sort
ListBox1.List = dar.toarray()
Set dar = Nothing
Set dar = CreateObject("System.Collections.ArrayList")
For Each r In Range("B2", Cells(Rows.Count, "B").End(xlUp))
dar.Add r.Value
Next
dar.Sort
ComboBox1.List = dar.toarray()
End Sub
This the workbook:
https://www.dropbox.com/s/ttm0pqmtcd...list.xlsm?dl=0
Bookmarks