Alphabetize a combo box list?
 

Anyone got code that will put the items in a combobox in alphabetical order?

Thanks!

Dan

Alphabetize a combo box list?
 

Dan,

In Excel, I've always used a rowsource that was a sorted list somewhere in
my workbook so the items didn't need to be sorted again. However, here is a
modified version of something I've used in a Word subroutine. The routine
below populates combobox1 found in a userform at the time the userform is
activated.

_____________________________

Private Sub UserForm_Activate()

Const adVarChar = 200
Const MaxCharacters = 255

Dim myArray As Variant
Dim myItem As Variant

myArray = Array("Tom", "Dick", "Harry")

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "myComboList", adVarChar, MaxCharacters
DataList.Open

For N = 0 To UBound(myArray)
DataList.AddNew
DataList("myComboList") = myArray(N)
DataList.Update
Next N

DataList.Sort = "myComboList"

DataList.MoveFirst
Do Until DataList.EOF
ComboBox1.AddItem (DataList.Fields.Item("myComboList"))
DataList.MoveNext
Loop

End Sub

____________________________

Steve Yandl


"Dan" wrote in message
...
Anyone got code that will put the items in a combobox in alphabetical
order?

Thanks!

Dan

Alphabetize a combo box list?
 

Steve -
Thanks so much. I was able to modify your code and get it incorporated in to
mine, and it does the trick. I have never used a recordset before, so I am
learning something new.
Dan

"Steve Yandl" wrote:

Dan,

In Excel, I've always used a rowsource that was a sorted list somewhere in
my workbook so the items didn't need to be sorted again. However, here is a
modified version of something I've used in a Word subroutine. The routine
below populates combobox1 found in a userform at the time the userform is
activated.

_____________________________

Private Sub UserForm_Activate()

Const adVarChar = 200
Const MaxCharacters = 255

Dim myArray As Variant
Dim myItem As Variant

myArray = Array("Tom", "Dick", "Harry")

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "myComboList", adVarChar, MaxCharacters
DataList.Open

For N = 0 To UBound(myArray)
DataList.AddNew
DataList("myComboList") = myArray(N)
DataList.Update
Next N

DataList.Sort = "myComboList"

DataList.MoveFirst
Do Until DataList.EOF
ComboBox1.AddItem (DataList.Fields.Item("myComboList"))
DataList.MoveNext
Loop

End Sub

____________________________

Steve Yandl


"Dan" wrote in message
...
Anyone got code that will put the items in a combobox in alphabetical
order?

Thanks!

Dan