sorting a listbox in a user form

[bizju]

Dear All,
I need to sort a listbox after populating it in a userform via the
macro below. The list varies from time to time.

Private Sub UserForm_Initialize()
UserForm1.ListBox1.AddItem "Jane"
UserForm1.ListBox1.AddItem "Anna"
UserForm1.ListBox1.AddItem "Melinda"
End Sub

Appreciate some help on this.

Thank you in advance
Julian

 

[Guest]

You will need to sort prior to adding to listbox. One option is to put your
data into a column in spreadsheet and use stndard Excel Sort. Then load
listbox from sorted data

e.g. Assume sorted data is in column A of Sheet1 starting row 1 (no header)

Private Sub UserForm_Initialize()
Dim lastrow as long, r as long
lastrow=Worksheets("Sheet1").cells(rows.count,"A").end(xlup).row
For ri= 1 to Lastrow
userform1.listbox1.additem worksheets("Sheet1").cells(r,"A")
next i
End sub

Alternatively, you could use a internal sort routine e.g a Bubble Sort.

HTH

 

[bizju]

Hi,
Actually I want to give users to option to sort as my macro loads all
sheet names to a list box in their order. Sorting will make the search
easier for some users while for others their original order would be
better. Is there any other way to sort?

Regards,
Julian

 

[Guest]

Hi,
You mentioned sheets in your last posting: this sorts worksheets
and puts sorted list into listbox. Load the array SheetNames with your data
(whether sheets or other data).

Hope you can adapt this to your needs.

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
listbox1.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

 

[bizju]

thanks. worked like a charm

regards,
julian