Sort values A-Z before populating the Listbox

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,323
Office Version
  1. 2007
Platform
  1. Windows
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 ?

Rich (BB code):
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,323
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
4,323
Office Version
  1. 2007
Platform
  1. Windows
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.

Rich (BB code):
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



Rich (BB code):
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

Well-known Member
Joined
Nov 16, 2010
Messages
4,323
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
4,323
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Attachments

  • 3479.jpg
    3479.jpg
    147 KB · Views: 4

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,323
Office Version
  1. 2007
Platform
  1. Windows
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

Rich (BB code):
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
 

Attachments

  • 3481.jpg
    3481.jpg
    220.2 KB · Views: 3

Forum statistics

Threads
1,176,287
Messages
5,902,328
Members
434,962
Latest member
sgilmoreBBP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top