Sort by Ascending - Descending not working

Question

I have this ComboBox for sort by: and a Command button for ascending and descending (Ex. Date, Location, Etc) but the problem is, it is not reflecting in the listbox1.

but it does work in actual sheet ("Data_Dispay") but not in userform listbox

meaning everytime i click the asc-desc button it sorts the actual sheet but not the listbox in userform

Here is the code for ascending and descending button

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

what i want to happen is to actually sort the listbox depending on the value of (Me.cmb_Sort_by) to ascending or descending

Answer 1

I have figured it out yesterday. I'm posting this code so if anyone encountered this kind of problem, well here is my solution.

'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