ExcelBanter - Sort Combobox List

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

"T De Villiers" wrote:

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

--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=503613