Sorting ListBox

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,126
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


VBA Code:
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Add this line as shown
Rich (BB code):
Set Dic = DicSortAscending(Dic)
With ListBox2
And use this function
VBA Code:
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

Well-known Member
Joined
Jan 29, 2008
Messages
1,126
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Just put the Function in a standard module & add the line in blue to your existing code where shown.
 

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,126

ADVERTISEMENT

Thank you so much.
Now works great.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,126

ADVERTISEMENT

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
 

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,126
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
That's a different question, so needs a new thread. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,126,928
Messages
5,621,635
Members
415,849
Latest member
PhoenixRising2015

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top