A-Z list box isnt in order

[ipbr21054]

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.

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]

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]

Hello,
If i apply filter then select A-Z it sorts fine in the column in question

Thanks

 

[MoshiM]

Hello,
If i apply filter then select A-Z it sorts fine in the column in question

Thanks

I meant specifically when you use

[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]

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]

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]

Hi,

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

Strange

 

[ipbr21054]

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]

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]

Hi,

take a look at these screen shots.

Let's try using an array list then

replace the stuff under your case else with:

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