CODEWITHSUNDEEP
vbasortingexcel
praxeo
praxeo

Reputation:

Sort combobox values alphabetically

I have a combobox in a userform for excel. What is the easiest way to sort it alphabetically? The values for it are hardcoded in vba and new ones are just added to the bottom so they are not in any kind of order already.

The userform is currently being used so that our users can import data from our database into excel. The combobox is there so they can specify which client data to import.

Upvotes: 3

Views: 55765

Answers (5)

kadrleyn
kadrleyn

Reputation: 394

It could easily be as follows :

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 have used in this template : Sort combobox alphabetically

enter image description here

Upvotes: 3

Bryan Gustafson
Bryan Gustafson

Reputation: 31

As you are adding them, compare them to the values already in the combobox. If they are less than the item you come across, the replace the item. If they are not less than, then move on until you find something the item is less than. If it cannot find the item, then 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:

Upvotes: 3

Dick Kusleika
Dick Kusleika

Reputation: 33165

Creating the array to sort isn't so hard as you might think. See Sorting Mulicolumn Listbox. You can put the List property right into a Variant type, sort that as an array, and dump that Variant Array back into the List property. Still not great, but it's the best VBA's got.

Upvotes: 2

shahkalpesh
shahkalpesh

Reputation: 33474

This uses ADO library, which I guess 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

Hope this gives you enough background to get started.
FYI - This is a disconnected recordset (simpler version of .net dataset for in memory tables).

Upvotes: 2

Tomalak
Tomalak

Reputation: 338326

VBA lacks a built-in sorting function for such things. Unfortunately.

One cheap way that does not involve implementing/using one of the popular sorting algorithms yourself is to use the .NET Framework's ArrayList class 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

Make this routine part of UserForm_Initialize. This will of course fail if the framework is not installed.

Upvotes: 1

Related Questions