How do I sort a combobox alphabetically?

[DreyFox]

Hello! I have a very simple combobox for which, I would like to sort alphabetically if possible. I was wondering if there is any VBA code that can achieve this?

Any help would be greatly appreciated. Thank you!

 

[Fluff]

Hi & welcome to MrExcel.
How are you populating the combo?

 

[DreyFox]

I found an online VBA code that populates it based on the checkboxes I pick. I found it on StackOverflow (I'd have to look it up again).

 

[Fluff]

I would need to see the code, or need to know how it should be populated.

 

[DreyFox]

I would need to see the code, or need to know how it should be populated.

Ah okay, here's the code:

im SearchString As String
Dim SearchRange As Range
Dim ans As String
Dim i As Long
If CheckBox37.Value = True Then
    SearchString = CheckBox37.Caption
    Set SearchRange = Sheets("Table of Part numbers").Range("A31:A38").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
    If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
    ans = SearchRange.Offset(0, 1).Value
    ComboBox1.AddItem ans
End If

If CheckBox37.Value = False Then
    SearchString = CheckBox37.Caption
    Set SearchRange = Sheets("Table of Part numbers").Range("A31:A38").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
    If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
    ans = SearchRange.Offset(0, 1).Value

For i = ComboBox1.ListCount - 1 To 0 Step -1
  If ComboBox1.List(i) = ans Then
    ComboBox1.RemoveItem (i)
  End If
Next
End If

 

[Fluff]

OK, try adding this to the end of that code

   Dim Ary As Variant
   Dim i As Variant
   Ary = Me.ComboBox1.List
   If IsArray(Ary) Then
      With CreateObject("system.collections.arraylist")
         For i = LBound(Ary) To UBound(Ary)
            .Add Ary(i, 0)
         Next i
         .Sort
         Me.ComboBox1.List = .toarray
      End With
   End If

 

[DreyFox]

OK, try adding this to the end of that code

   Dim Ary As Variant
   Dim i As Variant
   Ary = Me.ComboBox1.List
   If IsArray(Ary) Then
      With CreateObject("system.collections.arraylist")
         For i = LBound(Ary) To UBound(Ary)
            .Add Ary(i, 0)
         Next i
         .Sort
         Me.ComboBox1.List = .toarray
      End With
   End If

Would I add this code at the end of that code for every checkbox?

 

[Fluff]

No it needs to go after all the checkboxes.

 

[DreyFox]

No it needs to go after all the checkboxes.

Okay! I did that and it works great! Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.