VBA to sort combobox on user form

[omagoodness]

How can I alphabetically sort the combobox list on my user form? (Excel 2016)

 

[GTO]

Hi there,

Assuming you mean a combo box with Style set to fmStyleDropDownList, how are the values loaded?

Mark

 

[omagoodness]

Hi there,

Assuming you mean a combo box with Style set to fmStyleDropDownList, how are the values loaded?

Mark

Style is set to frmStyleDropDownCombo and values are loaded with RowSource property set to named table range. I tried sorting the table but the sort doesn't update when new rows are added.

I am very new to VBA. Just saying......

 

[GTO]

Style is set to frmStyleDropDownCombo and values are loaded with RowSource property set to named table range. I tried sorting the table but the sort doesn't update when new rows are added.

I am very new to VBA. Just saying......

Okay, just due to the time (I probably will be logging out in a bit), I will presume that the combo box is one-columned and suggest as follows.

Instead of using RowSource, let us use the values from the named range to add to the combo box in an ascending order. A simple Insert Sort if you will.

With a named range of: ListAnimals

In the Initialize Event of our form:

Option Explicit
  
Private Sub UserForm_Initialize()
Dim rngAnimalList As Range
Dim arrAnimalList
Dim n As Long
Dim Index As Long
Dim bolAdded As Boolean
  
  ' The named range represents B2:B56
  Set rngAnimalList = Sheet1.Range("ListAnimals")
  ' so thsi gives us a 2D array like myarray(1 to 55, 1 to 1)
  arrAnimalList = rngAnimalList.Value
  
  ' Now we just loop thru the array, and then loop thru any
  ' items already in the combo box and decide where to insert
  For n = LBound(arrAnimalList, 1) To UBound(arrAnimalList, 1)
    With Me.ComboBox1
      If .ListCount > 0 Then
        
        bolAdded = False
        For Index = 1 To .ListCount
          ' .List, .ListIndex etc are zero-based, hence the -1
          If arrAnimalList(n, 1)< .List(Index - 1) Then
            .AddItem arrAnimalList(n, 1), Index - 1
            bolAdded = True
            Exit For
          End If
        Next
        If Not bolAdded Then .AddItem arrAnimalList(n, 1)
      Else
        .AddItem arrAnimalList(n, 1)
      End If
    End With
  Next
End Sub

With our example named range looking like:

Excel Workbook
	B
1	Animal List
2	American Bulldog
3	Basset Hound
4	Black Widow Spider
5	Australian Cattle Dog
6	Airedale Terrier
7	Blue Whale
8	Black Russian Terrier
9	American Eskimo Dog
10	Angelfish
11	Bear
12	Anatolian Shepherd Dog
13	Anteater
14	Bedlington Terrier
15	Beaver
Sheet1

Excel 2010

Does that help?

Mark

 

[omagoodness]

The code did not produce an error but it also did not populate the combobox. BTW, my combobox has 2 columns with first column (record ID) set with 0 width property. The ID column is required for a Vlookup function. Any suggestions?

 

[GTO]

The code did not produce an error but it also did not populate the combobox. BTW, my combobox has 2 columns with first column (record ID) set with 0 width property. The ID column is required for a Vlookup function. Any suggestions?

I tested on a 2 column combo box and the previous code populates the first column. As you mention, you have the first column at zero width, so you might just not be seeing it...

Anyways, here is a new test range:

Excel Workbook
	A	B
1	ID	Animal List
2	1	American Bulldog
3	2	Basset Hound
4	3	Black Widow Spider
5	4	Australian Cattle Dog
6	5	Airedale Terrier
7	6	Blue Whale
8	7	Black Russian Terrier
9	8	American Eskimo Dog
10	9	Angelfish
11	10	Bear
12	11	Anatolian Shepherd Dog
13	12	Anteater
14	13	Bedlington Terrier
15	14	Beaver
Sheet1

Excel 2010

...with the named range: ListAnimals now representing A2:B15

And our UserForm's initialize code updated to:

Option Explicit
  
Private Sub UserForm_Initialize()
Dim rngAnimalList As Range
Dim arrAnimalList
Dim n As Long
Dim Index As Long
Dim bolAdded As Boolean
  
  ' The named range represents A2:B15
  Set rngAnimalList = Sheet1.Range("ListAnimals")
  ' so thsi gives us a 2D array like myarray(1 to 14, 1 to 2)
  arrAnimalList = rngAnimalList.Value
  
  ' Now we just loop thru the array, and then loop thru any
  ' items already in the combo box and decide where to insert
  For n = LBound(arrAnimalList, 1) To UBound(arrAnimalList, 1)
    With Me.ComboBox1
      
      .ColumnCount = 2
      .ColumnWidths = "0 pt;190 pt"
      .Width = 192
      
      
      If .ListCount > 0 Then
        
        bolAdded = False
        For Index = 1 To .ListCount
          ' .List, .ListIndex etc are zero-based, hence the -1
          ' Note that we are using column 2 in the array to sort/insert by, but that
          'we use the value in column 1 of the array for .AddItem to insert a new row
          ' in the combo box's list
          If arrAnimalList(n, 2)< .List(Index - 1, 1) Then
            .AddItem arrAnimalList(n, 1), Index - 1
            .List(Index - 1, 1) = arrAnimalList(n, 2)
            bolAdded = True
            Exit For
          End If
        Next
        If Not bolAdded Then
          .AddItem arrAnimalList(n, 1)
          .List(.ListCount - 1, 1) = arrAnimalList(n, 2)
        End If
      Else
        .AddItem arrAnimalList(n, 1)
        .List(0, 1) = arrAnimalList(n, 2)
      End If
    End With
  Next
End Sub

I will check back tonight to see if that worked out.

Mark

 

[omagoodness]

This code worked perfectly! I have 10 of the same combo boxes on the user form, named cboIng1, cboIng2, etc. Do I have to copy the code for each combobox or is there a loop I can use? I have been playing with something like:

Dim I as Long

for I = 1 to 10
with me("cboIng" & I)
next I
end with

but I don't know if this is the right way to go and how would it be used with your code?

 

[GTO]

It would be something like:

Private Sub UserForm_Initialize()
Dim i As Long
  
For i = 1 To 2
  With Me.Controls("ComboBox" & i)
    Debug.Print ".Top = " & .Top
  End With
Next
  
End Sub

That said, as you are likely providing 10 named ranges and thus, running thru 10 arrays, I would likely keep it simple rather than see how 'trick' I could make a loop.

Mark

 

[omagoodness]

Thanks Mark
Actually all of the 10 ComboBoxes refer to the same named range. The form is used by a chef to enter recipes and calculate a cost for each ingredient thereby getting a cost for the entire recipe. The form allows the selection of up to 10 ingredients (cboIng1, cboIng2, cboIng3, etc.). I have played around with inserting the loop you provided in your sort code; My sense tells me that it should be the outside loop with the sort code being run within each loop of the ComboBoxes. I tried the following, and got a

error 70: Permission Denied

. I think I know what that means but don't know how to find the error.

Private Sub Userform_Initialize()

Dim rngIngredientList As Range
Dim arrIngredientList
Dim n As Long
Dim Index As Long
Dim bolAdded As Boolean
Dim i As Long
  
  'this code sorts the combobox for ingredients
  Set rngIngredientList = Sheet2.Range("Ingredients")
  ' so this gives us a 2D array like myarray(1 to 14, 1 to 2)
  arrIngredientList = rngIngredientList.Value
  
   For n = LBound(arrIngredientList, 1) To UBound(arrIngredientList, 1)
    
     For i = 1 To 10
   With Me.Controls("cboIng" & i)
    Debug.Print ".Top= " & .Top
   
    .ColumnCount = 2
      .ColumnWidths = "0 pt;45 pt"
      .Width = 84
      
      If .ListCount > 0 Then
        
        bolAdded = False
        For Index = 1 To .ListCount
          
          If arrIngredientList(n, 2) < .List(Index - 1, 1) Then
            .AddItem arrIngredientList(n, 1), Index - 1
            .List(Index - 1, 1) = arrIngredientList(n, 2)
            bolAdded = True
            Exit For
          End If
         
            
        Next
        If Not bolAdded Then
          .AddItem arrIngredientList(n, 1)
          .List(.ListCount - 1, 1) = arrIngredientList(n, 2)
        End If
         
      Else
        .AddItem arrIngredientList(n, 1)
        .List(0, 1) = arrIngredientList(n, 2)
      End If
    End With
            Next i
  Next
 
End Sub

I really appreciate your help. Have a good day.

 

[GTO]

...Actually all of the 10 ComboBoxes refer to the same named range...I have played around with inserting the loop you provided in your sort code; My sense tells me that it should be the outside loop with the sort code being run within each loop of the ComboBoxes. I tried the following, and got a permission denied error

I changed to "ComboBox" for the constant string for my ease, but otherwise I believe I ran the code as you used it without error. I would double-check that the combo boxes are named as you believe they are, otherwise I am at a loss at the moment.

Aside from that and given that the same range is supplying the data for each boxes list, there is no reason to sort all of them. Fill the first box's list with the sorted values, then just plunk that box's list into all the other boxes.

Option Explicit
  
Private Sub Userform_Initialize()
Dim rngIngredientList As Range
Dim arrIngredientList
Dim n As Long
Dim Index As Long
Dim bolAdded As Boolean
  
  Set rngIngredientList = Sheet2.Range("Ingredients")
  arrIngredientList = rngIngredientList.Value
  
  For n = 1 To 10
    With Me.Controls(CStr("ComboBox" & n))
      .ColumnCount = 2
      .ColumnWidths = "0 pt;45 pt"
      .Width = 84
    End With
  Next n
  
  For n = LBound(arrIngredientList, 1) To UBound(arrIngredientList, 1)
    With Me.ComboBox1
      
      If .ListCount > 0 Then
        
        bolAdded = False
        
        For Index = 1 To .ListCount
          
          If arrIngredientList(n, 2) < .List(Index - 1, 1) Then
            .AddItem arrIngredientList(n, 1), Index - 1
            .List(Index - 1, 1) = arrIngredientList(n, 2)
            bolAdded = True
            Exit For
          End If
                
        Next
        
        If Not bolAdded Then
          .AddItem arrIngredientList(n, 1)
          .List(.ListCount - 1, 1) = arrIngredientList(n, 2)
        End If
        
      Else
        
        .AddItem arrIngredientList(n, 1)
        .List(0, 1) = arrIngredientList(n, 2)
      End If
    End With
  Next n
  
  For n = 2 To 10
    Me.Controls(CStr("Combobox" & n)).List = Me.ComboBox1.List
  Next
  
End Sub

Hope that helps,

Mark