Sort values A-Z before populating the Listbox

[ipbr21054]

Afternoon.
The following code does what i require in the way of what it should be doing BUT i would like to have the results sorted A-Z before populating the Listbox.

Currently when i tick the CheckBox the ListBox is populate like so,
ALEX
SHAUN
PETE
COLIN

Etc etc BUT i would like it to be like so,
ALEX
COLIN
PETE
SHAUN


The code below searches column G for the word YES
If you need to know the customers names are in column A
The range in use is A-M

I mention the above as i think but cant find the code that we sort A-Z in an used column THEN add to Listbox ?

Private Sub CheckBox1_Change()
If CheckBox1.Value = True Then
  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 CheckBox1.Value = "" Then Exit Sub
    Set r = Range("G3", Range("G" & Rows.Count).End(xlUp))
    Set f = r.Find("YES", 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(, -6).Value
          .List(i, 2) = f.Offset(, -5).Value
          .List(i, 3) = f.Offset(, -4).Value
          .List(i, 4) = f.Offset(, -3).Value
          .List(i, 5) = f.Offset(, 0).Value
          .List(i, 6) = f.Offset(, 1).Value
          
            added = True
            Exit For
          End Select
        Next
        If added = False Then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Offset(, -6).Value
          .List(.ListCount - 1, 2) = f.Offset(, -5).Value
          .List(.ListCount - 1, 3) = f.Offset(, -4).Value
          .List(.ListCount - 1, 4) = f.Offset(, -3).Value
          .List(.ListCount - 1, 5) = f.Offset(, 0).Value
          .List(.ListCount - 1, 6) = f.Offset(, 1).Value
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
      .TopIndex = 0
      Else
      MsgBox "NO SNIFF DATA WAS FOUND", vbCritical, "CLONING INFORMATION MESSAGE"
      CheckBox1.Value = ""
    End If
  End With
  End If
  If CheckBox1.Value = False Then
  With ListBox1
  .Clear
  End With
  End If
End Sub

 

[ipbr21054]

I have found a few codes on this group but no sort takes place.
Im wondering if its because there is a tick in tjhe checkbox.
Would this have any affect on the sorting in the listbox ?

Thanks

 

[ipbr21054]

So trying to continue with this sort A-Z in the listbox but getting into all kinds of a mess.

I have put the code now on a Command Button as supplied below.
This populates the data in the listbox but not in an A-Z order of what i would like.

So i am trying to copy the range in use of which is A3-H3 & down the page to N3-U3 & down the page.
Once that is done i will next attempth the sort.

My issue is that i can only copy over the values over if i select a range say A3-H25 but as this list will become longer i need the code to find the last entry then copy over.
I recorded a macro then added what i thought was correct in the line shown in red below.
I thought it would find the last row then copy the values in A3-H but i only get 1 value copied.
I changed the "A" for "H" but that also only copied 1 value over.

Private Sub CommandButton3_Click()
    Dim r As Range
    Dim sh As Worksheet
    Set sh = Sheets("DETAILS")
    sh.Select
    
    Set r = Range("A3", Range("A" & Rows.Count).End(xlUp))
    Selection.Copy
    Range("N3").Select
    ActiveSheet.Paste
    Range("L4").Select
        Range("N3:U20").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("DETAILS").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DETAILS").AutoFilter.Sort.SortFields.Add Key:= _
        Range("N3:N20"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("DETAILS").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    Selection.AutoFilter
End Sub

Private Sub CommandButton2_Click()
  TextBoxCUSTOMER.Value = ""
  TextBoxMAKE.Value = ""
  TextBoxMODEL.Value = ""
  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"
    Set r = Range("G3", Range("G" & Rows.Count).End(xlUp))
    Set f = r.Find("YES", LookIn:=xlValues, lookat:=xlWhole)
    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(, -6).Value
          .List(i, 2) = f.Offset(, -5).Value
          .List(i, 3) = f.Offset(, -4).Value
          .List(i, 4) = f.Offset(, -3).Value
          .List(i, 5) = f.Offset(, 0).Value
          .List(i, 6) = f.Offset(, 1).Value
            added = True
            Exit For
          End Select
        Next
        If added = False Then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Offset(, -6).Value
          .List(.ListCount - 1, 2) = f.Offset(, -5).Value
          .List(.ListCount - 1, 3) = f.Offset(, -4).Value
          .List(.ListCount - 1, 4) = f.Offset(, -3).Value
          .List(.ListCount - 1, 5) = f.Offset(, 0).Value
          .List(.ListCount - 1, 6) = f.Offset(, 1).Value
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
      .TopIndex = 0
      Else
      MsgBox "NO SNIFF DATA WAS FOUND", vbCritical, "CLONING INFORMATION MESSAGE"
    End If
  End With
End Sub

 

[ipbr21054]

Is anybody available to assist for a few
Minutes etc to help me with this.
I’ve tried various codes and searched most of the day but unable to find what I’m looking for or I stumble with code issues & unable to see why it failed.

To recap my goal is a sorted userform.
I have a working code that searches the worksheet column for matched values & then populates the listbox.
My issue is that once listbox is populated I’m then stuck with sorting customers names A - Z

I tried to have the code search the worksheet and copy each row & paste a few columns over then to sort A-Z then populate listbox.
I think that would work but I also failed in that.

Please advise if anybody can assist.
I can follow along to a point but starting in the right direction is my issue.

 

[ipbr21054]

Some information & screenshots for you.

Userform called CloningSearchForm
ListBox1
3 TextBoxes called, TextBoxCUSTOMER TextBoxMAKE TextBoxMODEL
Worksheet range is A3:H then down the page

TextBoxCUSTOMERS searches column A TextBoxMAKE searches column B TextBoxMODEL searches column C

Example of what happens
User types HONDA in TextBoxMAKE & the code searches column B & populates the Listbox1 But i would like the populated list to be sorted A-Z using the customers name but as you see the list isnt sorted.

So i thought to edit the existing code but copy & paste the matched values in another column, then search the column by customers name, now populate listbox so all in A-Z order.

 

[ipbr21054]

Morning,
Days trying to sort this but can you advise please.
Screenshot attached.

I type a value in the TextBoxMAKE
I then click the command button to search for this value in column B
The matched values & pasted & sorted A-Z on the worksheet called HELPERSHEET
The above works fine.

MY issue is that looking at the HELPERSHEET the order of items are correct BUT when they get imported to the listbox they are not in the same order, please see screenshot.

Can you see my mistake as to why the imported order is incorrect as it should be imported the same of how its on the HELPERSHEET
Thanks


Code in use is shown below

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("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