comboBox to display data in alphabetical order...

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows
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.

Code:
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.

Code:
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
 
Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Albert

How/where are you populating the combobox?
 

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows
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?




Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Albert

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

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi.

Sorry, completely missed your previous answer.

Code:
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.
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another option.
Add this to you initialise event
Code:
   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

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows
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?

Code:
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

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows
Hi Norie.

Did what you have suggested and it worked a treat.

Much appreciated.

Cheers.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
You need to set a reference to the sheet with the data
Code:
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
 

Forum statistics

Threads
1,136,968
Messages
5,678,872
Members
419,787
Latest member
juanam

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