A-Z list box isnt in order

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,048
Hi,

I have a worksheet which is sorted from old to new date in column A where A9 is 02/01/2017 & A978 is 07/10/2019
In column B are the customers names.

A code collects all the names column B & sorts them into an A-Z order, this takes place in column L

I have a userform where a field called NameForDateEntryBox shows the sorted customers from column L now in an A-Z order on the userform.

I believe the code supplied here is what you require.

Code:
Private Sub UserForm_Initialize()
Dim cl As Range
Dim rng As Range
Dim lstrw As Long
Dim lastrow As Long
Dim Lastrowa As Long
Dim cntr As Integer
Load PostageTransferSheet
TextBox2.SetFocus
'==============================================================================================
Application.ScreenUpdating = False
lastrow = Sheets("POSTAGE").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("POSTAGE").Cells(8, 2).Resize(lastrow - 7).Copy Sheets("POSTAGE").Cells(1, 12)
Lastrowa = Sheets("POSTAGE").Cells(Rows.Count, "L").End(xlUp).Row
Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Sort key1:=Cells(1, 12).Resize(Lastrowa), order1:=xlAscending, Header:=xlNo
CustomerSearchBox.List = Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Value
Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Clear
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
   cntr = 1
   With Sheets("POSTAGE")
      lstrw = .Range("B65536").End(xlUp).Row
      Set rng = .Range("B8:B" & lstrw)
      For Each cl In rng
         If cl.Offset(0, 5).Value = "" Then Sheets("POSTAGE").Range("L" & cntr).Value = cl.Value: cntr = cntr + 1
      Next
      Select Case cntr
         Case 1
            NameForDateEntryBox.Clear
            MsgBox "POSTAGE LIST IS NOW EMPTY OF CUSTOMERS NAMES", vbExclamation, "POSTAGE LIST NO NAMES MESSAGE"
            Unload PostageTransferSheet
         Case 2
            NameForDateEntryBox.Clear
            NameForDateEntryBox.AddItem .Range("L1").Value
            .Range("L1").Clear
         Case Else
            .Range("L1:L" & cntr - 1).Sort key1:=.Range("L1"), order1:=xlAscending, Header:=xlNo
            NameForDateEntryBox.List = .Range("L1:L" & cntr - 1).Value
            .Range("L1:L" & cntr - 1).Clear
      End Select
   End With
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Application.ScreenUpdating = True


TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
TextBox7.Value = Format(CDbl(Date), "dd/mm/yyyy")
End Sub

See screen shot of what i mean.

 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
292
Hi,

I have a worksheet which is sorted from old to new date in column A where A9 is 02/01/2017 & A978 is 07/10/2019
In column B are the customers names.

A code collects all the names column B & sorts them into an A-Z order, this takes place in column L

I have a userform where a field called NameForDateEntryBox shows the sorted customers from column L now in an A-Z order on the userform.
When you use .sort on the range does it sort properly?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,048
Hello,
If i apply filter then select A-Z it sorts fine in the column in question

Thanks
 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
292
Hello,
If i apply filter then select A-Z it sorts fine in the column in question

Thanks
I meant specifically when you use
Code:
[COLOR=#333333].Range("L1:L" & cntr - 1).Sort key1:=.Range("L1"), order1:=xlAscending, Header:=xlNo[/COLOR]
Using this line to sort the data and then adding that range to the list box on my own computer with dummy data similar to what you've shown in your example results in the proper output.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,048
Can you advise how i then check that as it runs from start to finish and i dont see where im supposed to see the ordered list ?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,048
Hi,

I put exit sub after that line then looked at L1
The list is in the same order as i type on worksheet
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,048
Hi,

Ive now removed that exit sub & tried again and now its in order ?

Strange
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,048
Hi,
So strange in fact without doing anything its happened again.

This time i put the row number in each cell

What i noticed is that it doesnt like row 982
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,048
Hi,

take a look at these screen shots.

When i run the code row 982 is out of order




If i add exit sub in the line of code then look in L1 you will see that row 982 isnt even there

 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
292
Hi,

take a look at these screen shots.
Let's try using an array list then

replace the stuff under your case else with:
Code:
dim Temp_A as variant, LL as object

Set ll = CreateObject("System.Collections.ArrayList")

Temp_A = .Range("L1:L" & cntr - 1).Value

For x = 1 To UBound(temp_a, 1) 
    ll.Add temp_a(x, 1)
next x

ll.Sort
NameForDateEntryBox.List = ll.toarray

.Range("L1:L" & cntr - 1).Clear
 
Last edited:

Forum statistics

Threads
1,086,063
Messages
5,387,580
Members
402,070
Latest member
hyperf0

Some videos you may like

This Week's Hot Topics

Top