ListBox1 imports values in the incorrect order

[ipbr21054]

Evening,

My worksheet has values in column A like so
ANDY
BOB
CHARLIE
DAVE

When imported to Listbox1 i then see like so
DAVE
CHARLIE
BOB
ANDY

So i decided to reverse the order on the worksheet like so.
DAVE
CHARLIE
BOB
ANDY

Now im thinking they will be reversed & then shown correctly in the listbox1

Nope
They are still shown like so
DAVE
CHARLIE
BOB
ANDY

Can you explain how the order on the worksheet can be changed but the values are always imported only one way ?

Thanks

 

[My Aswer Is This]

Show us the code you're using to import the values into the listbox

 

[ipbr21054]

Here we go thanks.

Private Sub CommandButton2_Click()
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   Dim Fnd As Range
   Dim i As Long, Qty As Long, Nrow As Long
   Dim Str As String
   
   Set Ws1 = Sheets("DETAILS")
   Set Ws2 = Sheets("HELPERSHEET")
   Set Fnd = Ws1.Range("B" & Rows.Count)
   Str = TextBoxMAKE.Value
   
   If Str = "" Then Exit Sub
   Ws2.Cells.Clear
   
   Qty = WorksheetFunction.CountIf(Ws1.Columns("B"), Str)
   
   Nrow = 2
   For i = 1 To Qty
      Nrow = Nrow + 1
      Set Fnd = Ws1.Range("B:B").Find(Str, Fnd, , xlWhole, xlByRows, xlNext, False, , False)
      Ws2.Cells(Nrow, 1).Resize(, 8).Value = Fnd.Offset(, -1).Resize(, 8).Value
   Next i
   
  With ListBox1
    .Clear
    .ColumnCount = 7
    .ColumnWidths = "0;180;150;150;100;80;60"
    If TextBoxMAKE.Value = "" Then Exit Sub
    Worksheets("HELPERSHEET").Activate
    Set r = Range("B1", Range("B" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBoxMAKE.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
              .List(i, 1) = f.Offset(, -1).Value
              .List(i, 2) = f.Offset(, 0).Value
              .List(i, 3) = f.Offset(, 1).Value
              .List(i, 4) = f.Offset(, 2).Value
              .List(i, 5) = f.Offset(, 5).Value
              .List(i, 6) = f.Offset(, 6).Value
              added = True
              Exit For
          End Select
        Next
        If added = False Then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Offset(, -1).Value
          .List(.ListCount - 1, 2) = f.Offset(, 0).Value
          .List(.ListCount - 1, 3) = f.Offset(, 1).Value
          .List(.ListCount - 1, 4) = f.Offset(, 2).Value
          .List(.ListCount - 1, 5) = f.Offset(, 5).Value
          .List(.ListCount - 1, 6) = f.Offset(, 6).Value
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
      TextBoxSearch = UCase(TextBoxSearch)
      .TopIndex = 0
      Else
      MsgBox "NO MAKE WAS FOUND", vbCritical, "CLONING INFORMATION MESSAGE"
      TextBoxMAKE.Value = ""
      TextBoxMAKE.SetFocus
    End If
    End With
End Sub

 

[My Aswer Is This]

Wow:
That's a lot of code to load a listBox
Do not even want to ask what all this means.
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[ipbr21054]

Here is the original code but my issue again is the Listbox shows Descending as opposed Asending

Private Sub TextBoxMAKE_Change()
TextBoxMAKE = UCase(TextBoxMAKE)
  Dim r As Range, f As Range, cell As String, added As Boolean
  Dim sh As Worksheet
  
  Set sh = Sheets("DETAILS")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 7
    .ColumnWidths = "0;180;150;150;100;80;60"
    If TextBoxMAKE.Value = "" Then Exit Sub
    Set r = Range("B3", Range("B" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBoxMAKE.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
              .List(i, 1) = f.Offset(, -1).Value
              .List(i, 2) = f.Offset(, 0).Value
              .List(i, 3) = f.Offset(, 1).Value
              .List(i, 4) = f.Offset(, 2).Value
              .List(i, 5) = f.Offset(, 5).Value
              .List(i, 6) = f.Offset(, 6).Value
              added = True
              Exit For
          End Select
        Next
        If added = False Then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Offset(, -1).Value
          .List(.ListCount - 1, 2) = f.Offset(, 0).Value
          .List(.ListCount - 1, 3) = f.Offset(, 1).Value
          .List(.ListCount - 1, 4) = f.Offset(, 2).Value
          .List(.ListCount - 1, 5) = f.Offset(, 5).Value
          .List(.ListCount - 1, 6) = f.Offset(, 6).Value
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
      TextBoxSearch = UCase(TextBoxSearch)
      .TopIndex = 0
      Else
      MsgBox "NO MAKE WAS FOUND", vbCritical, "CLONING INFORMATION MESSAGE"
      TextBoxMAKE.Value = ""
      TextBoxMAKE.SetFocus
    End If
  End With
End Sub

 

[ipbr21054]

A code to reverse the listbox order would be fine