Sort Combobox List

[T De Villiers]

Hi,

I would like to sort a combox List by ascending order.
Currently, my macro does the following fine, need
additional code to sort the data, many thks

Private Sub ComboBox1_DropButtonClick()
Dim sh As Worksheet

ComboBox1.Clear
For Each sh In ActiveWorkbook.Worksheets
ComboBox1.AddItem sh.Name

Next
End Sub

 

[Guest]

Hi,

HTH

Sub SortSheets()

' This routine sorts the sheets of the
' active workbook in ascending order.

Dim SheetNames() As String

' Get the number of sheets
SheetCount = ActiveWorkbook.Sheets.Count

' Redimension the arrays
ReDim SheetNames(1 To SheetCount)


' Fill array with sheet names
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
Next i

'
' Sort the array in ascending order
Call BubbleSort(SheetNames)

For i = 1 To SheetCount
Combobox1.additem SheetNames(i)
Next i


End Sub



Sub BubbleSort(List() As String)
' Sorts the List array in ascending order
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp

First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
For j = i + 1 To Last
If List(i) > List(j) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Next i
End Sub