ListBox_Excel_Sort

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

CodeFunctionName

What is this?

Public

Tested

Original Work

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

ListBoxControlName, Optional Order_Asc1_Desc2 = 1, Optional TempWBName = "This", optional TempSheetName = "Data", Optional TempSheetColumn = "A"