Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting a listbox in a user form
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting a listbox in a user form
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 " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting a listbox in a user form
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 Toppers wrote: 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 " wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting a listbox in a user form
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 " wrote: 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 Toppers wrote: 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 " wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting a listbox in a user form
thanks. worked like a charm
regards, julian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Which User Form Listbox selection just checked? | Excel Programming | |||
Filter Listbox in user form | Excel Programming | |||
User form with a listbox | Excel Programming | |||
Adding items to a spreadsheet from a user form listbox | Excel Programming | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |