Anyone got a quick idea on a swift way to sort a ListBox of dates? Problem is that I'm populating the list with elements from another ListBox. I'm looking for something more efficient than a bubble sort, a quicksort I found on the web didn't work, and using a temporary range in a sheet isn't an option. So far my attempts have only brought headaches. Any help would be appreciated.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Sort a ListBox of Dates? 2
- Thread starter logius
- Start date
-
1
- #2
Hi!
More information on what you are trying to accomplish might help. It seems to me that any list appropriate for a list box will go through a bubble sort in considerably less than a second anyway. And the bubble sort does have the advantage of maintaining any secondary sort that might already exist in the list. One more question. Where does the information from the original list box come from? Can that list be sorted?
hth
Jeff Bridgham
More information on what you are trying to accomplish might help. It seems to me that any list appropriate for a list box will go through a bubble sort in considerably less than a second anyway. And the bubble sort does have the advantage of maintaining any secondary sort that might already exist in the list. One more question. Where does the information from the original list box come from? Can that list be sorted?
hth
Jeff Bridgham
- Thread starter
- #3
I've been trying to tell the boss that a bubble sort wouldn't be as inefficient as he thinks for our purposes, but so far haven't had any luck. That was my first thought for the sorting algorithm, though. As for the source of the list, that comes the first ListBox (who's contents come from a defined range on a spreadsheet) but that first box is sorted already.
What I'm really getting at is that I'm switching data between the two ListBoxes. Users select dates from the first one and dump them into the second one using a command button and remove them using a second button. Problem is, when users try to modify their ListBox entries (add or subtract from their defined date range), the removed items are added to the bottom of the receiving box. This causes other problems later on. Basically, I need the second box to be in order before I can move on.
What I'm really getting at is that I'm switching data between the two ListBoxes. Users select dates from the first one and dump them into the second one using a command button and remove them using a second button. Problem is, when users try to modify their ListBox entries (add or subtract from their defined date range), the removed items are added to the bottom of the receiving box. This causes other problems later on. Basically, I need the second box to be in order before I can move on.
Hi!
Well, the bad news is that list boxes don't have a Sort property. Most people sort them by basing there rowsource on a query and sorting the query. Obviously that will not work in this case. I would probably put the data in an array first and use a bubble sort or a binary sort on the array and push the data into the list box. Another option is to import the data into a table or link to the spreadsheet and write some code to set up a query which will get the data you want already sorted.
hth
Jeff Bridgham
Well, the bad news is that list boxes don't have a Sort property. Most people sort them by basing there rowsource on a query and sorting the query. Obviously that will not work in this case. I would probably put the data in an array first and use a bubble sort or a binary sort on the array and push the data into the list box. Another option is to import the data into a table or link to the spreadsheet and write some code to set up a query which will get the data you want already sorted.
hth
Jeff Bridgham
-
1
- #5
DaleWatson123321
Programmer
I just had to "take a stab at this one" - partly because I've never used a "bubble sort".
My contribution doesn't use the bubble sort, but nonetheless my code seems to work.
The code follows, but if you would like the actual sample file I created, don't hesitate to ask.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
=============
START OF CODE
=============
Dim ch As String
Dim blnk As String
Dim num As Double
Sub Add_To_Second()
'add item chosen from 1st list to the "Second List"
Application.ScreenUpdating = False
ch = Application.Range("choice"
.Value
'user choice from First List
Application.Goto Reference:="second_list"
num = Application.Range("existnum".Value
'pulls value from =COUNTA(second_list) formula
'in "existnum"
ActiveCell.Offset(num, 0).Select
'goes to 1st blank row, based on above COUNT
ActiveCell.Value = ch
Sort_Second_List 'subroutine below
Range("a1".Select
Application.ScreenUpdating = True
End Sub
Sub Sort_Second_List() 'sorts the Second List
Application.Goto Reference:="second_list"
Selection.Sort Key1:="Second_List", _
Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub
My contribution doesn't use the bubble sort, but nonetheless my code seems to work.
The code follows, but if you would like the actual sample file I created, don't hesitate to ask.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
=============
START OF CODE
=============
Dim ch As String
Dim blnk As String
Dim num As Double
Sub Add_To_Second()
'add item chosen from 1st list to the "Second List"
Application.ScreenUpdating = False
ch = Application.Range("choice"
.Value'user choice from First List
Application.Goto Reference:="second_list"
num = Application.Range("existnum"
.Value'pulls value from =COUNTA(second_list) formula
'in "existnum"
ActiveCell.Offset(num, 0).Select
'goes to 1st blank row, based on above COUNT
ActiveCell.Value = ch
Sort_Second_List 'subroutine below
Range("a1"
.SelectApplication.ScreenUpdating = True
End Sub
Sub Sort_Second_List() 'sorts the Second List
Application.Goto Reference:="second_list"
Selection.Sort Key1:="Second_List", _
Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub
DaleWatson123321
Programmer
Update...
I had set up the file with an "Add to Second List" button, but had't completed the routine for a button to "Remove From Second List".
I have now completed the code for the "Remove" button, and it is as follows:
Sub Remove_From_Second()
Application.ScreenUpdating = False
Application.Goto Reference:="second_list"
ch = Application.Range("second_choice".Value - 1
ActiveCell.Offset(ch, 0).Select
ActiveCell.ClearContents
Sort_Second_List
Range("a1".Select
Application.ScreenUpdating = True
End Sub
Again, "seeing is believing", so if you'd like a copy of the file, please don't hesitate to ask.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
I had set up the file with an "Add to Second List" button, but had't completed the routine for a button to "Remove From Second List".
I have now completed the code for the "Remove" button, and it is as follows:
Sub Remove_From_Second()
Application.ScreenUpdating = False
Application.Goto Reference:="second_list"
ch = Application.Range("second_choice"
.Value - 1ActiveCell.Offset(ch, 0).Select
ActiveCell.ClearContents
Sort_Second_List
Range("a1"
.SelectApplication.ScreenUpdating = True
End Sub
Again, "seeing is believing", so if you'd like a copy of the file, please don't hesitate to ask.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
- Thread starter
- #7
- Thread starter
- #8
Uhhhh, I think I forgot to mention that the ListBoxes are used in a UserForm and not actually on a spreadsheet. Not only that, but the sheet I get the data from will be generated into a .CSV file which will be different every time it's created, and adding formulas in isn't an option. My boss wants/needs the file to remain as a "clean" .CSV. Thanks for the help, though, Dale. Thanks to you too, jebry.
- Status
Similar threads
- Locked
- Question