Sort Userform ListBox

Question

My Data has 4 columns with the following headings: Heading 1,Heading 2,Heading 3 and Heading 4.

I have created a Listbox using the below code. In the userform i have 2 Option Buttons which indicates 2 columns. I want to sort my listbox based on the selection. Example: if I select the 'serial number' option box, it should filter based on the first column value (Heading 1) and if I choose 'job', it should filter based on the third column value (Heading 3).

Can anybody help me to find a code for that?

Please see below the code for the listbox.

Private Sub UserForm_Initialize() 

    With ListBox1 
        .ColumnCount = 4 
        .ColumnWidths = "130;30;30;130" 
    End With 

    LstRow = Cells(Rows.Count, 1).End(xlUp).Row 

    For a = 0 To LstRow - 2 
        b = a + 2 
        ListBox1.AddItem 
        ListBox1.list(a, 0) = Cells(b, 4) 
        ListBox1.list(a, 1) = Cells(b, 1) 
        ListBox1.list(a, 2) = Cells(b, 3) 
        ListBox1.list(a, 3) = Cells(b, 2) 

    Next a 

End Sub

I'd recommend that you sort your data before loading it into the form like so: https://stackoverflow.com/questions/21451458/vba-excel-sort-range-by-specific-column/21451501#21451501 Alternatively, you can also use any of the following approaches to sort your data "in memory" before loading it into the form: https://stackoverflow.com/questions/37239107/sort-range-without-sorting-it-in-a-spreadsheet/37239386#37239386 Another approach could be to use `ListViews` instead of a `ListBox` which allow for sorting with `lvwAscending` https://stackoverflow.com/a/36646730/1153513 — Ralph, Oct 30 '17 at 12:09
Refer to this: [SortingArrays](http://www.cpearson.com/excel/SortingArrays.aspx) and [ListBox Utilities](http://www.cpearson.com/excel/ListBoxUtils.aspx). Source: www.cpearson.com/Excel/MainPage.aspx Copyright 2017, Charles H. Pearson — danieltakeshi, Oct 30 '17 at 12:25
Definitely agree with Ralph and Daniel Takeshi. It is much quicker to do a sort on a range then by use of any bubble sort method. Sort it before it is added to the listbox. — John Muggins, Oct 30 '17 at 12:35
Thanks for your reply, but I cannot sort the excel file before loading to the form. Is there any other way to sort after loading or while loading to the form. But still i will look in to the link given by danielatakeshi - Jamshad — Jamshad, Oct 30 '17 at 15:34
@Jamshad My first link (provided above) shows how to sort the data before loading it into the ListBox. The second link shows how to sort the data can be sorted while loading it into the ListBox. The third link shows how to sort the data after it has been loaded into a ListView (instead of a ListBox). So, it would seem to me that all of your questions have been addressed. Please consider *reading* the comments / links and come back here if you have more / different questions (afterwards). — Ralph, Oct 31 '17 at 09:06
yes you can! read the whole range in an array variable : `Arr = range(cells(2,1) , cells(LstRow,4).value` . And then first sort it (google search quicksort or bubble sort). And Finally, add it to the Listbox : `Listbox1.List = Arr`. Voilà, and it's even faster than your original code ! — Patrick Lepelletier, Oct 31 '17 at 09:28

Sort Userform ListBox

0 Answers0