Sorting Combo Box Values ​​Alphabetically
I have a combobox in a custom form for excel. What's the easiest way to sort alphabetically? The values ​​for it are hardcoded in vba and new ones are just added from below, so they are no longer in any order.
A custom form is currently being used to enable our users to import data from our database into excel. The combobox exists, so they can specify which customer data to import.
Making an array to sort is not as difficult as you might think. See Sorting a Mulicolumn List . You can put a List property directly into a Variant type, sort it as an array, and dump that Variant Array back into a List property. Still not great, but it got the best VBA.
a source to share
As you add them, compare them with the values ​​already in the combobox. If they are smaller than the item you are encountering, replace the item. If they are not less, then move on until you find something that is smaller than the subject. If it can't find the item, add it to the end.
For X = 0 To COMBOBOX.ListCount - 1
COMBOBOX.ListIndex = X
If NEWVALUE < COMBOBOX.Value Then
COMBOBOX.AddItem (NEWVALUE), X
GoTo SKIPHERE
End If
Next X
COMBOBOX.AddItem (NEWVALUE)
SKIPHERE:
a source to share
This is using the ADO library which I believe will be available on most computers (with Excel installed).
Sub SortSomeData()
Dim rstData As New ADODB.Recordset
rstData.Fields.Append "Name", adVarChar, 40
rstData.Fields.Append "Age", adInteger
rstData.Open
rstData.AddNew
rstData.Fields("Name") = "Kalpesh"
rstData.Fields("Age") = 30
rstData.Update
rstData.AddNew
rstData.Fields("Name") = "Jon"
rstData.Fields("Age") = 29
rstData.Update
rstData.AddNew
rstData.Fields("Name") = "praxeo"
rstData.Fields("Age") = 1
rstData.Update
MsgBox rstData.RecordCount
Call printData(rstData)
Debug.Print vbCrLf & "Name DESC"
rstData.Sort = "Name DESC"
Call printData(rstData)
Debug.Print vbCrLf & "Name ASC"
rstData.Sort = "Name ASC"
Call printData(rstData)
Debug.Print vbCrLf & "Age ASC"
rstData.Sort = "Age ASC"
Call printData(rstData)
Debug.Print vbCrLf & "Age DESC"
rstData.Sort = "Age DESC"
Call printData(rstData)
End Sub
Sub printData(ByVal data As Recordset)
Debug.Print data.GetString
End Sub
Hopefully this will give you enough background to get started.
FYI is a disabled recordset (a simpler version of the .net dataset for memory tables).
a source to share
VBA doesn't have a built-in sort function for this kind of thing. Unfortunately.
One cheap way that doesn't involve implementing / using one of the popular sorting algorithms is to use the .NET Framework class ArrayList
via COM:
Sub test()
Dim l As Object
Set l = CreateObject("System.Collections.ArrayList")
''# these would be the items from your combobox, obviously
''# ... add them with a for loop
l.Add "d"
l.Add "c"
l.Add "b"
l.Add "a"
l.Sort
''# now clear your combobox
Dim k As Variant
For Each k In l
''# add the sorted items back to your combobox instead
Debug.Print k
Next k
End Sub
Do this regular part UserForm_Initialize
. This is of course a failure if the infrastructure is not installed.
a source to share
It could be easy:
Sub fill_combobox()
Dim LastRow, a, b As Long, c As Variant
ComboBox1.Clear
LastRow = Sheets("S1").Cells(Rows.Count, 2).End(xlUp).Row
For x = 2 To LastRow
ComboBox1.AddItem Cells(x, 2).Value
Next
For a = 0 To ComboBox1.ListCount - 1
For b = a To ComboBox1.ListCount - 1
If ComboBox1.List(b) < ComboBox1.List(a) Then
c = ComboBox1.List(a)
ComboBox1.List(a) = ComboBox1.List(b)
ComboBox1.List(b) = c
End If
Next
Next
End Sub
I used in this template: Add Items to Custom Combobox Form Alphabetically Enter Image Description Here
a source to share