ComboBox drop down where list should be Surname then First name

[ipbr21054]

Hi,
I currently have a ComboBox drop down on my worksheet which when i click on the drop down i see the list of customers from A-Z in the order of First name then Surname.

I need the same but Surname then First name.
I have copied the existing code & changed the relevant items but need some assistance for how it should be reversed.

This is the code in use.

Private Sub ComboBox2_Change()
  Dim r As Range
  Set r = Range("B7", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible) _
    .Find(ComboBox2.Value)
  If Not r Is Nothing Then r.Select
  ComboBox2.ListIndex = -1
End Sub
Private Sub ComboBox2_DropButt*******()
  RangeUniqueSortFillControl Range("B7", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible), Sheet13.ComboBox2
End Sub

If i am unable to use the above but there is another way then please advise.

have a nice day

 

[NoSparks]

Try replacing your existing RangeTo1dArray function with this

Function RangeTo1dArray(aRange As Range) As Variant
  Dim a() As Variant, C As Range, i As Long
  Dim ReversedName As String, N As Variant, x As Long
  ReDim a(0 To aRange.Cells.Count - 1)
  i = i - 1
  For Each C In aRange
    N = Split(C)
        For x = UBound(N) To LBound(N) Step -1
            ReversedName = ReversedName & " " & N(x)
        Next x
        ReversedName = Mid(ReversedName, 2)
    i = i + 1
    a(i) = ReversedName
    ReversedName = ""
  Next C
  RangeTo1dArray = a()
End Function

 

[ipbr21054]

Can you show me please using my code as in post 1

 

[ipbr21054]

Hi,
Would thay be this part,

Private Sub ComboBox2_Change()  Dim r As Range
  Set r = Range("B7", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible) _
    .Find(ComboBox2.Value)
  If Not r Is Nothing Then r.Select
  ComboBox2.ListIndex = -1
End Sub

Or this part,

Private Sub ComboBox2_DropButt*******()  RangeUniqueSortFillControl Range("B7", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible), Sheet13.ComboBox2
End Sub

 

[NoSparks]

Can you show me please using my code as in post 1

No.
Your problem is not what you posted, it's within one of the functions used by the sub that is called in what you posted.

 

[ipbr21054]

So looking on my worksheet the only code that we are looking for must be this ?

Private Sub SortCustomerAZ_Click()    
    Dim x As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("POSTAGE")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        .Range("A8:I" & x).Sort key1:=Range("A8"), order1:=xlAscending, Header:=xlGuess
        
    End With
                      
    ActiveWorkbook.Save
    
    Application.ScreenUpdating = True
    Sheets("POSTAGE").Range("A8").Select
    
End Sub

 

[NoSparks]

No.
Put a breakpoint within your Private Sub ComboBox2_DropButt*******() and use F8 to see where the code takes you.

 

[NoSparks]

I see in your cross post at Ozgrid you have abandon this.

 

[ipbr21054]

You are correct as i couldnt get it to work / flow smootly.
I wouldnt say cross post as not in the same group and was looking for a fresh pair of eyes etc to help.

I am awaiting on Dante to advise on his code which looks very simple but at present will not populate the listbox