Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

https://stackoverflow.com/questions/345315/vba-sorting-the-data-in-a-listbox-sort-works-but-data-in-listbox-not-changed

Private Sub UserForm_Initialize()

 Call HideTitleBar(Me)

 Set ExtraFiltersDic = CreateObject("scripting.dictionary")
 ExtraFiltersDic.CompareMode = 1
 Set ExtraFiltersDic = GetExtraFiltersDic()

 Dim k
 For Each k In ExtraFiltersDic.Keys
  ListBox1.AddItem k
 Next
 
 Call SortListBox(ListBox1, ListBox2, ExtraFiltersDic)
  
End Sub
Public Sub SortListBox(ByRef ListBox As MSForms.ListBox, Optional ByRef ListBox2 As MSForms.ListBox, Optional ByRef RelationalDic As Object)

 Dim First As Integer, Last As Integer, NumItems As Integer
 Dim i As Integer, j As Integer
 Dim TempArray() As Variant, Temp As String

 ReDim TempArray(ListBox.ListCount)

 First = LBound(TempArray)
 Last = UBound(TempArray) - 1
 For i = First To Last
    TempArray(i) = ListBox.List(i)
 Next i
 
 For i = First To Last
    For j = i + 1 To Last
        If TempArray(i) > TempArray(j) Then
            Temp = TempArray(j)
            TempArray(j) = TempArray(i)
            TempArray(i) = Temp
        End If
    Next j
 Next i

 ListBox.Clear

 If Not ListBox2 Is Nothing And Not RelationalDic Is Nothing Then
  Set KeyValDic = CreateObject("scripting.dictionary")
  Set KeyValDic = RelationalDic
 End If

 For i = First To Last
    ListBox.AddItem TempArray(i)
    If Not ListBox2 Is Nothing And Not RelationalDic Is Nothing Then
     ListBox2.AddItem KeyValDic(TempArray(i))
    End If
 Next i

End Sub
  • No labels