ListBox Sort by Ascending - Descending

bry02 · Nov 26th 2022

Good day everyone so i have this problem, i have a listbox1 with combo box which has a value that will sort by ascending or descending buttons .
i have a code but it does not sort the actual listbox1, instead what it does is he is sorting the actual sheet.

here is the code and picture

Code

Private Sub CommandButton4_Click()

Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Data_Display")
Dim col_number As Integer
col_number = Application.WorksheetFunction.Match(Me.cmb_Sort_by.Value, dsh.Range("1:1"), 0)

dsh.UsedRange.Sort key1:=dsh.Cells(1, col_number), order1:=xlAscending, Header:=xlYes

End Sub

Private Sub CommandButton5_Click()
Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Data_Display")

Dim col_number As Integer
col_number = Application.WorksheetFunction.Match(Me.cmb_Sort_by.Value, dsh.Range("1:1"), 0)

dsh.UsedRange.Sort key1:=dsh.Cells(1, col_number), order1:=xlDescending, Header:=xlYes

End Sub


**here is my code in the list box**


rivate Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Columns("A:B").EntireColumn.AutoFit

Me.txt_id.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)    
Me.cmb_week.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)     
Me.cmb_line.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)     
Me.cmb_machine.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)    
Me.txt_time.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)    
Me.txt_minutes.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 5)    
Me.txt_desc.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 6)    
Me.txt_product.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 7)    
Me.cmb_factor.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 8) 
       
Me.txt_Open_Date.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 9), "D-MMM-YYYY")        
If Me.ListBox1.List(Me.ListBox1.ListIndex, 10) <> "" Then        

Me.txt_Close_Date.Value = Format(Me.ListBox1.List(Me.ListBox1.ListIndex, 10), "D-MMM-YYYY")    

End If 
           
Me.txt_mrf.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 11)    
Me.txt_possible_cause.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 12)    
Me.txt_corrective_action.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 13)    
Me.txt_action.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 14)    
Me.txt_incharge.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 15)    
Me.txt_duedate.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 16)   
Me.cmb_Status.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 17)    
Me.txt_note.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 18)

End Sub



**here is the code related to cmb_sort_by**


Sub Refresh_DropDown_List()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("List")

'''''''''' Sort by List
With Me.cmb_Sort_by    
.Clear    
.AddItem "ID"    
.AddItem "Description"    
.AddItem "Factor"    
.AddItem "Week"    
.AddItem "Incharge"    
.AddItem "Open Date"    
.AddItem "Product"   
.AddItem "Closed Date"   
.AddItem "Machine"    
.AddItem "Line"    
.AddItem "Status"    
.AddItem "Possible Cause"    
.AddItem "Update Time"    
.Value = "ID"        

End With
End Sub

Display More

**Carim** · Nov 26th 2022

Hello,

The code you posted has been designed to sort cells in your Sheet "Data_Display"

The question you are asking is quite different ... since you need to sort a ListBox after the ListBox has been populated ...

This reminds me of a set of re-usable solutions designed by MVP Mark Proctor - Exceloffthegrid

You can find them at following address :

Sorting ListBoxes with VBA - Excel Off The Grid

I received a question from a reader recently, who wanted to sort a Listbox using VBA. As this seemed like a common problem which many users could face, I

exceloffthegrid.com

Hope this will help

bry02 · Nov 26th 2022

Quote from Carim

Hello,

The code you posted has been designed to sort cells in your Sheet "Data_Display"

The question you are asking is quite different ... since you need to sort a ListBox after the ListBox has been populated ...

This reminds of a set of re-usable solutions designed by MVP Mark Proctor - Exceloffthegrid

You can find them at following address :
https://exceloffthegrid.com/sorting-listboxes-with-vba/

Hope this will help

Display More

thank you for your response, however my buttons for sorting is not designed for sorting Excel sheets.

what i need is the button to sort my list box depending on the value given by combobox

**Carim** · Nov 26th 2022

Would you mind attaching your sample workbook for review ...

bry02 · Nov 27th 2022

Quote from Carim

Would you mind attaching your sample workbook for review ...

Hi Carim, i just saw your reply right now, i figured it out yesterday when i was playing with it. and found a solution.

here is my code if anyone needs it or encountered the same problem.

Code

'sort ascending

Private Sub CommandButton4_Click()

Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Data_Display")
Dim col_number As Integer
col_number = Application.WorksheetFunction.Match(Me.cmb_Sort_by.Value, dsh.Range("1:1"), 0)

dsh.UsedRange.Sort key1:=dsh.Cells(1, col_number), order1:=xlAscending, Header:=xlYes

Dim lr As Long
lr = Application.WorksheetFunction.CountA(dsh.Range("A:A"))

If lr = 1 Then lr = 2

With Me.ListBox1    
.ColumnHeads = True    
.ColumnCount = 20    
.ColumnWidths = "35,40,70,250,70,70,240,200,200,70,70,70,200,200,200,200,70,70,200,100  "    
.TextAlign = fmTextAlignCenter    
.RowSource = "Data_Display!A2:T" & lr
    
End With


End Sub

'sorth descending

Private Sub CommandButton5_Click()
Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Data_Display")

Dim col_number As Integer
col_number = Application.WorksheetFunction.Match(Me.cmb_Sort_by.Value, dsh.Range("1:1"), 0)

dsh.UsedRange.Sort key1:=dsh.Cells(1, col_number), order1:=xlDescending, Header:=xlYes

Dim lr As Long

lr = Application.WorksheetFunction.CountA(dsh.Range("A:A"))

If lr = 1 Then lr = 2

With Me.ListBox1    
.ColumnHeads = True    
.ColumnCount = 20    
.ColumnWidths = "35,40,70,250,70,70,240,200,200,70,70,70,200,200,200,200,70,70,200,100  "    
.TextAlign = fmTextAlignCenter    
.RowSource = "Data_Display!A2:T" & lr
    
End With

End Sub

Display More

so basically as you can see, i just copy my RowSourse so it will automatically update or refresh.

**Carim** · Nov 27th 2022

Glad to hear you could fix your problem

Thanks a lot for sharing your solution for all Future readers !!!

ListBox Sort by Ascending - Descending

Carim Nov 26th 2022

Participate now!

Share

Similar Threads

Row color change if specific cells have any value

Ascending and Ascending & FilterBy and SortBy Buttons

Review/critique/offer suggestions to skydive logbook - US $56

THE SAFETY OF ELECTRIC SKATEBOARDS

Tags

Users Viewing This Thread