comboBox to display data in alphabetical order...

[albertc30]

Hi all.

I am trying to show combboxes on userform to display the data, say customers, in alphabetical order.

I have found the code bellow on MrExcel but it's not working.

I am getting run time error 13, type mismatch.

Private Sub UserForm_Initialize()

    ufcbbname.SetFocus
    allowed.Text = Worksheets("Saved Invoices").Range("I7")
    allcustomerowed.Text = Worksheets("Saved Invoices").Range("K7")
    
    
            'bellow to try and show customers combbox in alphabetical order
    
        Dim unsorted As Boolean, i As Integer, temp As Variant
        unsorted = True
        With ufcbbname
        Do
        unsorted = False
        For i = 0 To UBound(.List) - 1
        If .List(i) > .List(i + 1) Then
        temp = .List(i)
        .List(i) = .List(i + 1)
        .List(i + 1) = temp
        unsorted = True
        Exit For
        End If
        Next i
        Loop While unsorted = True
        End With
    
            'above to try and show customers combbox in alphabetical order
    


End Sub

I also read that the above code would return the data in the combBox in alphabetical order without messing with the data in the sheet.

On another website, I have read that excel VBA cannot display a combBox in alphabetical order?
Is this the case?

Any help is, as always, truly appreciated.

Cheers

P.S; The list is shown by the code bellow.

Private Sub ufcbbname_Change()

    'upcustdetails.Enabled = True


        'when data changes on the field, all other related values are retrived.
    
    Dim i As Long, lastrow As Long
        lastrow = Sheets("Customers").Range("A" & Rows.Count).End(xlUp).Row
        For i = 2 To lastrow
  
        If Sheets("Customers").Cells(i, "A").Value = (Me.ufcbbname) Or _
        Sheets("Customers").Cells(i, "A").Value = Val(Me.ufcbbname) Then
        Me.cutbncn = Sheets("Customers").Cells(i, "L").Value
        Me.tbtotalspent = Sheets("Customers").Cells(i, "r").Value
        Me.tboutinvoices = Sheets("Customers").Cells(i, "q").Value
        Me.tvTotalSpent = Sheets("Customers").Cells(i, "w").Value
        Me.tbCreatedBy = Sheets("Customers").Cells(i, "u").Value
        Me.tblastupdate = Sheets("Customers").Cells(i, "s").Value
        Me.tbcrebydate = Sheets("Customers").Cells(i, "v").Value
        Me.tbupdaby = Sheets("Customers").Cells(i, "t").Value
        'Me.allowed = Sheets("Saved_Invoices").Cells(i, "H7").Value
        'Me.tbncem = Sheets("Customers").Cells(i, "H").Value
        End If
  
        'upcustdetails.SetFocus
  
        ufcbbname.Text = UCase(ufcbbname.Text)
  
        Next


End Sub

 

[Norie]

Albert

How/where are you populating the combobox?

 

[albertc30]

Hi.

I have found a way to have the comboBox display data alphabeticly by sorting the data out via a filter.

However, the code bellow will not work correctly as it will add 1 to the value contained within the very last cell.

Instead of it doing this way, can it just count the number of cells with data and use that to add a 1 for the next userID?

Private Sub UserForm_Initialize()

    adcubton.Enabled = False
              
              'testing code to auto creat user id
    
    Dim LstRow As Long 'Figure out Last Value in Range
    Dim OVal As String 'Old Value
    Dim NVal As String 'New Value
    Dim wsh As Worksheet
        Set wsh = Worksheets("customers")
    


        With wsh
            If .Range("I2") = "" Then
                .Range("I2") = "LMMR0001"
                    Else
                LstRow = .Cells(Rows.Count, "I").End(xlUp).Row
                OVal = .Range("I" & LstRow)
                NVal = "LMMR" & Format(Right(OVal, 4) + 1, "0000")
                'Me.tbncid.Value = NVal
                Me.cbcusid.Value = NVal
            End If
        End With
    
            'testing code to auto creat user id
            
        tbcuscount.Text = Worksheets("Customers").Range("N2")
            


End Sub

Cheers.

 

[Norie]

Albert

You can populate a combobox with data sorted alphabetically without sorting it on the sheet.

 

[albertc30]

Hi.

Sorry, completely missed your previous answer.

Private Sub ufcbbname_DropButt*******()

        'dropdown list for customer combo box


    Dim i As Long, lastrow As Long
    lastrow = Sheets("Customers").Range("A" & Rows.Count).End(xlUp).Row
    If Me.ufcbbname.ListCount = 0 Then
    For i = 2 To lastrow
    Me.ufcbbname.AddItem Sheets("Customers").Cells(i, "A").Value
    Next i
    End If


End Sub

Thanks.

Private Sub ufcbbname_DropButt******* - don't know why the code is changing click to ****** here when paste.

 

[Norie]

Albert

What you should do is move the code to populate the combobox ufcbbname into the Initialize event of the userform.

If you do that then the code for sorting that's already may work.

Private Sub UserForm_Initialize()
Dim unsorted As Boolean, temp As Variant
Dim i As Long, lastrow As Long

    allowed.Text = Worksheets("Saved Invoices").Range("I7")

    allcustomerowed.Text = Worksheets("Saved Invoices").Range("K7")
    
    ' populate customer name combobox

    lastrow = Sheets("Customers").Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To lastrow
        Me.ufcbbname.AddItem Sheets("Customers").Cells(i, "A").Value
    Next i
  
    ' sort customer combobox

    unsorted = True

    With ufcbbname
        Do
            unsorted = False
            For i = 0 To UBound(.List) - 1
                If .List(i) > .List(i + 1) Then
                    temp = .List(i)
                    .List(i) = .List(i + 1)
                    .List(i + 1) = temp
                    unsorted = True
                    Exit For
                End If
            Next i
        Loop While unsorted = True
    End With

    ufcbbname.SetFocus

End Sub

 

[Fluff]

Another option.
Add this to you initialise event

   Dim cl As Range
   Dim Lst As Object
   
   Set Lst = CreateObject("system.collections.arraylist")
      For Each cl In wsh.Range("A2", wsh.Range("A" & Rows.Count).End(xlUp))
         Lst.Add cl.Value
      Next cl
   Lst.Sort
   Me.ufcbbname.List = Lst.toarray

And remove the DropButton event

 

[albertc30]

Hi Fluff.

I did what you have said but got an error.

I believe this to be because there isn't a specific worksheet being specified?

Private Sub UserForm_Initialize()

    ufcbbname.SetFocus
    allowed.Text = Worksheets("Saved Invoices").Range("I7")
    allcustomerowed.Text = Worksheets("Saved Invoices").Range("K7")
    
        'show list and in alpha order
    
    Dim cl As Range
    Dim Lst As Object
   
    Set Lst = CreateObject("system.collections.arraylist")
      For Each cl In wsh.Range("A2", wsh.Range("A" & Rows.Count).End(xlUp))
         Lst.Add cl.Value
      Next cl
   Lst.Sort
   Me.ufcbbname.List = Lst.toarray
    
     'show list and in alpha order
         
End Sub

Many thanks for your time and apologies for the delay as I went away for a week with the family.

Cheers.

 

[albertc30]

Hi Norie.

Did what you have suggested and it worked a treat.

Much appreciated.

Cheers.

 

[Fluff]

You need to set a reference to the sheet with the data

Private Sub UserForm_Initialize()

    ufcbbname.SetFocus
    allowed.Text = Worksheets("Saved Invoices").Range("I7")
    allcustomerowed.Text = Worksheets("Saved Invoices").Range("K7")
    
        'show list and in alpha order
    
    Dim cl As Range
    Dim Lst As Object
    [COLOR=#0000ff]Dim Wsh As Worksheet
    
    Set Wsh = Sheets("Customers")[/COLOR]
    Set Lst = CreateObject("system.collections.arraylist")
      For Each cl In Wsh.Range("A2", Wsh.Range("A" & Rows.Count).End(xlUp))
         Lst.Add cl.Value
      Next cl
   Lst.Sort
   Me.ufcbbname.List = Lst.toarray
    
     'show list and in alpha order
         
End Sub