Solved: ListBox Sort

[Philcjr]

Hello all, :hi:

I need some help with sorting dates in a listbox. Here is my code. I have tried the compare with and without using the 'DateValue' as I thought trying to compare strings was the issue. This routine runs after a date is added to the listbox.

I am lost please help.




Dim vHoliday As Variant, vMemory As Variant
Dim Y As Long, Z As Long

'Populates the array with the values in the list box
vMemory = Array(HolidayListBox.List)

'Defines the first and last values in the array
For Y = LBound(vMemory) To UBound(vMemory)
'looks at the next value in the array
For Z = Y + 1 To UBound(vMemory)
'Compares the values and swaps if need be
If DateValue(vMemory(Y)) > DateValue(vMemory(Z)) Then
vHoliday = vMemory(Z)
vMemory(Z) = vMemory(Y)
vMemory(Y) = vHoliday
End If
Next Z
Next Y

'loads the holidays from vMemory into vHoliday
vHoliday = vMemory

'Populates the listbox with the sorted values
HolidayListBox.List = vHoliday

[austenr]

Can you post a sample workbook?

[Philcjr]

Here you go. This is not the actual file... as the other is much to large to upload. Hopefully, this should give you an idea.

Thanks,
Phil

[Bob Phillips]

Works for me


Sub vbn()
Dim vHoliday As Variant, vMemory As Variant
Dim Y As Long, Z As Long

'Populates the array with the values in the list box
vMemory = HolidayListBox.List

'Defines the first and last values in the array
For Y = LBound(vMemory) To UBound(vMemory)
'looks at the next value in the array
For Z = Y + 1 To UBound(vMemory)
'Compares the values and swaps if need be
If vMemory(Y, 0) > vMemory(Z, 0) Then
vHoliday = vMemory(Z, 0)
vMemory(Z, 0) = vMemory(Y, 0)
vMemory(Y, 0) = vHoliday
End If
Next Z
Next Y

'loads the holidays from vMemory into vHoliday
vHoliday = vMemory

'Populates the listbox with the sorted values
With HolidayListBox
.RowSource = ""
For Y = LBound(vMemory) To UBound(vMemory)
If Not IsEmpty(vMemory(Y, 0)) Then
If IsNumeric(vMemory(Y, 0)) Then
.AddItem Format(vMemory(Y, 0), "dd mmm yyyy")
Else
.AddItem vMemory(Y, 0)
End If
End If
Next Y
End With
End Sub

[Philcjr]

Thank you Bob, I will test and let everyone know.

[Philcjr]

Works like a charm. I changed one line so dates would be in ascending order.

From

'Compares the values and swaps if need be
If vMemory(Y, 0) > vMemory(Z, 0) Then


To

'Compares the values and swaps if need be
If vMemory(Y, 0) < vMemory(Z, 0) Then


Thanks again

[Philcjr]

Upon further testing, I had to change the above line of code to the following:


If DateValue(vMemory(Y, 0)) > DateValue(vMemory(Z, 0)) Then


I dont know what I was thinking before.:doh:

[Bob Phillips]

Upon further testing, I had to change the above line of code to the following:


If DateValue(vMemory(Y, 0)) > DateValue(vMemory(Z, 0)) Then


I dont know what I was thinking before.:doh:

Why, previously it just tested dates, at least in mine it did. How do you load the Listbox?

[Philcjr]

Bob,

This is how I load the listbox.


HolidayListBox.AddItem MonthBox.Value & " " & DayBox.Value & ", " & Right(ThisWorkbook.Sheets("Calendar").Range("Q98"), 4)


My hunch is that I am populating the listbox with a string of text vs a date.

At first try it worked unitl I tried to sort the following dates:
January 18, 2006
January 19, 2006
January 2, 2006
January 20, 2006

After I used the DateValue it worked.

I guess I should have converted the string to date prior to loading into the listbox.

[Bob Phillips]

Bob,

This is how I load the listbox.


HolidayListBox.AddItem MonthBox.Value & " " & DayBox.Value & ", " & Right(ThisWorkbook.Sheets("Calendar").Range("Q98"), 4)


My hunch is that I am populating the listbox with a string of text vs a date.

At first try it worked unitl I tried to sort the following dates:
January 18, 2006
January 19, 2006
January 2, 2006
January 20, 2006

After I used the DateValue it worked.

I guess I should have converted the string to date prior to loading into the listbox.

That sounds about right. I used a range of dates in my test, which is why I was able to do a straight comparison of value, and also why I had to unset the RowSource property otherwise I couldn't load the array into the ListBox.