Sort items in Fm20 Listbox using Excel spreadsheet Sort function.
Can decide what sheet, workbook and column, also what order to sort by, ascending or descending.
Was needed yesterday to do sort on fly when user adds item to list
' Function need to put inside UserForm module, to access that Listbox control
Sub ListBox_Excel_Sort(ListBoxControlName, Optional Order_Asc1_Desc2 = 1, Optional TempWBName = "This", optional TempSheetName = "Data", Optional TempSheetColumn = "A")
' Sort list in ListBoxControlName then refresh it by using Excel Sort feature in TempSheetName spreadsheet
' TempSheetName expected to be in workbook TempWBName and have column TempSheetColumn with a header value in row 1
' Function will clear area TempSheetColumn from row 2 to row 50000
'
' Save list in [TempWBName]TempSheetName!TempSheetColumn starting row 2' Settings!AA
' Sort it
' Read it back again
'
If TempWBName = "This" Then TempWBName = ThisWorkbook.Name
OOrd = xlAscending
If Order_Asc1_Desc2 = 2 Then OOrd = xlDescending
Workbooks(TempWBName).Worksheets(TempSheetName).Range(TempSheetColumn & 2, TempSheetColumn & 50000).ClearContents
For I = 1 To Controls(ListBoxControlName).Listcount ' Lst_CoPeers.ListCount
Workbooks(TempWBName).Worksheets(TempSheetName).Range(TempSheetColumn & 1).Offset(I).Value = Controls(ListBoxControlName).List(I - 1)
Next
Max1 = CountColumnCells(TempSheetColumn, TempWBName, TempSheetName, 2)
' Sort it
Workbooks(TempWBName).Worksheets(TempSheetName).Range(TempSheetColumn & 2, TempSheetColumn & 50000).Sort Workbooks(TempWBName).Worksheets(TempSheetName).Range(TempSheetColumn & 1), OOrd, , , , , , xlNo
' Or you can use SortArea1
' Sort1Col TempSheetColumn, Order_Asc1_Desc2, TempSheetName, TempWBName
' read it back again
Controls(ListBoxControlName).Clear
X1 = 1
Do Until X1 > Max1
Controls(ListBoxControlName).AddItem Workbooks(TempWBName).Worksheets(TempSheetName).Range(TempSheetColumn & 1).Offset(X1).Value
X1 = X1 + 1
Loop
End Sub
' Sort list in ListBoxControlName then refresh it by using Excel Sort feature in TempSheetName spreadsheet
' TempSheetName expected to be in workbook TempWBName and have column TempSheetColumn with a header value in row 1
' Function will clear area TempSheetColumn from row 2 to row 50000
'
' Save list in [TempWBName]TempSheetName!TempSheetColumn starting row 2' Settings!AA
' Sort it
' Read it back again
'
If TempWBName = "This" Then TempWBName = ThisWorkbook.Name
OOrd = xlAscending
If Order_Asc1_Desc2 = 2 Then OOrd = xlDescending
Workbooks(TempWBName).Worksheets(TempSheetName).Range(TempSheetColumn & 2, TempSheetColumn & 50000).ClearContents
For I = 1 To Controls(ListBoxControlName).Listcount ' Lst_CoPeers.ListCount
Workbooks(TempWBName).Worksheets(TempSheetName).Range(TempSheetColumn & 1).Offset(I).Value = Controls(ListBoxControlName).List(I - 1)
Next
Max1 = CountColumnCells(TempSheetColumn, TempWBName, TempSheetName, 2)
' Sort it
Workbooks(TempWBName).Worksheets(TempSheetName).Range(TempSheetColumn & 2, TempSheetColumn & 50000).Sort Workbooks(TempWBName).Worksheets(TempSheetName).Range(TempSheetColumn & 1), OOrd, , , , , , xlNo
' Or you can use SortArea1
' Sort1Col TempSheetColumn, Order_Asc1_Desc2, TempSheetName, TempWBName
' read it back again
Controls(ListBoxControlName).Clear
X1 = 1
Do Until X1 > Max1
Controls(ListBoxControlName).AddItem Workbooks(TempWBName).Worksheets(TempSheetName).Range(TempSheetColumn & 1).Offset(X1).Value
X1 = X1 + 1
Loop
End Sub
ListBoxControlName, Optional Order_Asc1_Desc2 = 1, Optional TempWBName = "This", optional TempSheetName = "Data", Optional TempSheetColumn = "A"
Views 809
Downloads 331
CodeID
DB ID