Listbox on userform to show results in reverse order

[ipbr21054]

I have a userform where the user types a customers name in a Textbox.
Using a command button the code searches for that typed value & then lists the results in the Listbox.
The results are shown as per screenshot where you can see from day 1 a low row number & also an old date.
The user is then at present having to scroll for a while to see newer results.

My question is can the results be reversed so as opposed older values at the top then scroll to new have it so new values are at the top then scroll to older results.
Maybe the search is looking from Row 7 down the page to todays current row which is 2312 when maybe we can alter it to search from last current row with values up to row 7,just a thought/

The code for the Listbox is supplied below.

Private Sub CommandButton2_Click()
      Dim r As Range, f As Range, Cell As String, added As Boolean
      Dim sh As Worksheet
      
      Set sh = Sheets("POSTAGE")
      sh.Select
      With ListBox1
        .Clear
        .ColumnCount = 4
        .ColumnWidths = "220;130;160;10"
        If TextBox8.Value = "" Then Exit Sub
        Set r = Range("B8", Range("B" & Rows.count).End(xlUp))
        Set f = r.Find(TextBox8.Value, LookIn:=xlValues, LookAt:=xlPart)
        If Not f Is Nothing Then
          Cell = f.Address
          Do
            added = False
            For i = 0 To .ListCount - 1
              Select Case StrComp(.List(i), f.Value, vbTextCompare)
                Case 0, 1
                  .AddItem f.Value, i                 'col B
                  .List(i, 1) = f.Offset(, 2).Value   'col D
                  .List(i, 2) = f.Offset(, 5).Text    'col G .TEXT is used as date in Listbox was shown as 12/17/2020 as opposed 17/12/2020
                  .List(i, 3) = f.Row                 'row
                  added = True
                  Exit For
              End Select
            Next
            If added = False Then
              .AddItem f.Value                                 'col B
              .List(.ListCount - 1, 1) = f.Offset(, 2).Value   'col D
              .List(.ListCount - 1, 2) = f.Offset(, 5).Text    'col G .TEXT is used as date in Listbox was shown as 12/17/2020 as opposed 17/12/2020
              .List(.ListCount - 1, 3) = f.Row                 'row
            End If
            Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> Cell
          TextBox8 = UCase(TextBox8)
          .TopIndex = 0
        Else
          MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
          TextBox8.Value = ""
          TextBox8.SetFocus
      End If
      End With
      
End Sub

 

[ipbr21054]

@Fuji
In post #5 a memeber asked "why are you using the AddItem method in two different ways?"
The original code shown below was what i was advised a while ago so just used it.
Ive also used it on a few other forms so it would be nice to now fix it.

So can you show me the code like your post in #9 BUT from the Top then Down the page.

Maybe highlite which part of the code alter Top to Bottom / Bottom to Top
ALSO
The column in userform that is Sorted

Private Sub CommandButton2_Click()
      Dim r As Range, f As Range, Cell As String, added As Boolean
      Dim sh As Worksheet
      
      Set sh = Sheets("POSTAGE")
      sh.Select
      With ListBox1
        .Clear
        .ColumnCount = 4
        .ColumnWidths = "220;130;160;10"
        If TextBox8.Value = "" Then Exit Sub
        Set r = Range("B8", Range("B" & Rows.count).End(xlUp))
        Set f = r.Find(TextBox8.Value, LookIn:=xlValues, LookAt:=xlPart)
        If Not f Is Nothing Then
          Cell = f.Address
          Do
            added = False
            For i = 0 To .ListCount - 1
              Select Case StrComp(.List(i), f.Value, vbTextCompare)
                Case 0, 1
                  .AddItem f.Value, i                 'col B
                  .List(i, 1) = f.Offset(, 2).Value   'col D
                  .List(i, 2) = f.Offset(, 5).Text    'col G .TEXT is used as date in Listbox was shown as 12/17/2020 as opposed 17/12/2020
                  .List(i, 3) = f.Row                 'row
                  added = True
                  Exit For
              End Select
            Next
            If added = False Then
              .AddItem f.Value                                 'col B
              .List(.ListCount - 1, 1) = f.Offset(, 2).Value   'col D
              .List(.ListCount - 1, 2) = f.Offset(, 5).Text    'col G .TEXT is used as date in Listbox was shown as 12/17/2020 as opposed 17/12/2020
              .List(.ListCount - 1, 3) = f.Row                 'row
            End If
            Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> Cell
          TextBox8 = UCase(TextBox8)
          .TopIndex = 0
        Else
          MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
          TextBox8.Value = ""
          TextBox8.SetFocus
      End If
      End With
      
End Sub

 

[NoSparks]

The original code shown below was what i was advised a while ago so just used it.
Ive also used it on a few other forms so it would be nice to now fix it.

Would sure like to know what your request was that got you that code.

That is a neat procedure.
No mater how your sheet is sorted, the list is always filtered (as per textbox8) and sorted (A-Z)
Too bad it doesn't have comments within the code.

I tested it against the file you linked to in July.
Using a wildcard (*) in textbox8 and got all customers sorted.
Even noticed that customer Nick Robinson is at the top due to a leading space in the name.

 

[ipbr21054]

Thanks for that, Nick Robinson now edited with no space.
How does the solution code look to you ?

Its much shorter & does what i need.
If its ok i think i will start to replace the code that was used on my other forms but i will see your reply fisrt.
Ive worked out my question that i asked about now

 

[NoSparks]

How does the solution code look to you ?

It's just fine.

 

[Fuji]

Try delete current code and replace with the following.
If you enter any cahracter(s), it will show all the related rows in ascending order.
CommandButton1 will reverse the order by row reference in 4the column.

Private Sub TextBox8_Change()
    Dim a, s, i&, ii&, n&, w, f$
    Me.ListBox1.Clear
    s = Me.TextBox8
    If s = "" Then Exit Sub
    f = "mm/dd/yyyy"
    With Sheets("POSTAGE")
        With .Range("a1", .Cells.SpecialCells(11))
            a = Application.Index(.Value2, Evaluate("row(1:" & .Rows.Count & ")"), Array(2, 4, 6, 6))
        End With
        ReDim w(1 To 4, 1 To .Rows.Count)
    End With
    For i = 7 To UBound(a, 1)
        If InStr(1, a(i, 1), s, 1) Then
            n = n + 1
            w(1, n) = a(i, 1): w(2, n) = a(i, 2)
            w(3, n) = Format(a(i, 3), f): w(4, n) = i
        End If
    Next
    If n = 0 Then Exit Sub
    ReDim Preserve w(1 To 4, 1 To n)
    With Me.ListBox1
        .ColumnCount = 4
        .ColumnWidths = "220;130;160;10"
        .Column = w
    End With
End Sub


Private Sub CommandButton1_Click()
    Dim w, x
    If Me.ListBox1.ListCount < 2 Then Exit Sub
    x = Me.ListBox1.List
    mySort x, UBound(x, 2)
    Me.ListBox1.List = x
End Sub

Sub mySort(x, ref)
    Dim i&, ii&, iii&, temp
    ref = ref
    For i = LBound(x) To UBound(x, 1) - 1
        For ii = i + 1 To UBound(x, 1)
            If Val(x(i, ref)) < Val(x(ii, ref)) Then
                For iii = 1 To UBound(x, 2)
                    temp = x(i, iii): x(i, iii) = x(ii, iii): x(ii, iii) = temp
                Next
            End If
        Next
    Next
End Sub

 

[ipbr21054]

Thanks but i like the idea of a shorter code so im going to use the solution code.
I was just asking for the same code but from Top then Down.
I can & will use on my other forms

 

[Fuji]

Do as you want, but shoter code is not always better or faster.