Sorting ListBox

[asyamonique]

Hello,
Is it possible to sort the datas from A to Z when the ListBox2 has been populated?
On the ListBox2 first column is populating the names
Many Thanks

Private Sub CommandButton12_Click()
Application.ScreenUpdating = False

On Error Resume Next

Dim J As Long, Dic As Object, rng As Range, Ac As Integer
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
Set rng = Sheets("overtimelist").Range("A:A").CurrentRegion.Resize(, 6)
ReDim nRay(1 To rng.Count, 1 To 6)
For J = 1 To rng.Count
    If Not Dic.Exists(rng(J, 1).Value) Then
       Dic.Add rng(J, 1).Value, rng(J, 5).Text
    Else
        Dic(rng(J, 1).Value) = Dic(rng(J, 1).Value) + rng(J, 5).Value
    End If
        For Ac = 1 To 6
            nRay(J, Ac) = rng(J, Ac).Text
        Next Ac
Next J
With ListBox1
    .ColumnCount = 6
    .ColumnWidths = "140;100;90;100;70;0"
    .list = nRay
End With
With ListBox2
    .ColumnCount = 2
    .ColumnWidths = "150;80"
    .list = Application.Transpose(Array(Dic.keys, Dic.Items))
 End With

Application.ScreenUpdating = True
End Sub

 

[Fluff]

Add this line as shown

Set Dic = DicSortAscending(Dic)
With ListBox2

And use this function

Function DicSortAscending(InDic As Object) As Object
   Dim Ky As Variant
   Dim AryLst As Object
   Dim i As Long
   
   Set AryLst = CreateObject("System.Collections.ArrayList")
   If InDic.Count > 1 Then
      With AryLst
         For Each Ky In InDic.Keys
            .Add Ky
         Next Ky
         .Sort
         Set DicSortAscending = CreateObject("Scripting.Dictionary")
         For i = 0 To .Count - 1
            DicSortAscending.Add .Item(i), InDic(.Item(i))
         Next
      End With
   Else
      DicSortAscending = InDic
   End If
End Function

 

[asyamonique]

Hello,
Thanks for the reply.
Could you please help me more on that coz I don't know where to add this code exactly.
Thanks again.

 

[Fluff]

Just put the Function in a standard module & add the line in blue to your existing code where shown.

 

[asyamonique]

Thank you so much.
Now works great.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[asyamonique]

You're welcome & thanks for the feedback.

Hello Fluff'
I would like to ask you the reason why this code doesn't work if the file running at windows10?
Cheers

 

[Fluff]

It does work on Win 10 as that is what I have. You may need to download & install .Net Framework 3.5

Download .NET Framework 3.5 from Official Microsoft Download Center

Microsoft .NET Framework 3.5 contains many new features building incrementally upon .NET Framework 2.0 and 3.0, and includes .NET Framework 2.0 service pack 1 and .NET Framework 3.0 service pack 1.

www.microsoft.com

 

[asyamonique]

Ok I will try,
How can I use that code to sort on second column if there are numbers also from largest to smalest?
Many Thanks

 

[Fluff]

That's a different question, so needs a new thread. Thanks