Combo Box question

[Sam]

I have a list of dates that has several of the same date, however they are in
chronological order, named DateList. A combo box in a userform displays these
with the UserForm_Initialize event:

For each c in Range("DateList")
If c <> c.Offset(-1, 0) Then
Me.cBoxDates.AddItem Format(c, "m/d/yyyy")
End If
Next

How can I get the combo box to display the dates in reverse order (latest to
earliest) if the list is earliest to latest?

Thanks,

Sam

 

[Dave Peterson]

You could sort the data yourself before you add it to the combobox.

John Walkenbach shows a way to use a collection in this code:
http://www.j-walk.com/ss/excel/tips/tip47.htm

He's actually filling a listbox with that unique list that's sorted, but you
could modify the code to sort in reverse order.

Or you could loop through your range in reverse order.

If that DateList is a single column, you could use:

Option Explicit
Private Sub UserForm_Initialize()
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

With ActiveSheet.Range("DateList")
FirstRow = .Row
LastRow = .Cells(.Cells.Count).Row

For iRow = LastRow To FirstRow Step -1
If .Cells(iRow).Value <> .Cells(iRow - 1).Value Then
Me.cBoxDates.AddItem Format(.Cells(iRow).Value, "m/d/yyyy")
End If
Next iRow
End With
End Sub

===
Although, I'd use a unambiguous date format like: mmm dd, yyyy

If that range is multiarea or multicolumn, then the code would have to change.

 

[FSt1]

hi
technically this is more a for next question than a combo box question
but....hey...
you will have to modify your for next loop and add a couple of lines....
Dim c As Range
Set c = Range("A65000").End(xlUp) 'or find end of Datelist
For i = Range("datelist").Rows.Count To 1 Step -1
If c <> c.Offset(-1, 0) Then
Me.cBoxDates.AddItem Format(c, "m/d/yyyy")
Set c = c.Offset(-1, 0) ' move up 1
End If
Next

i have comments on the lines you need to add with explinations

regards
FSt1

 

[FSt1]

opps. correction.
the code i posted works well provided there are no dupilicates.
here is modified code to handle duplicates.
Private Sub UserForm_Initialize()
Dim c As Range
Set c = Range("A65000").End(xlUp) 'or find end of Datelist
For i = Range("datelist").Rows.Count To 1 Step -1
If c <> c.Offset(-1, 0) Then
Me.cBoxDates.AddItem Format(c, "m/d/yyyy"
End If
Set c = c.Offset(-1, 0) 'move up 1
Next
End Sub

slight over sight on my part. sorry.

regards
FSt1

 

[Sam]

Thanks a lot for your help. This is exactly what I need.

Sam

 

[Helmut Meukel]

Sam,

there is a simple solution, add the item bevore all other items in the list.
See the changed code below.

Helmut.

I have a list of dates that has several of the same date, however they are in
chronological order, named DateList. A combo box in a userform displays these
with the UserForm_Initialize event:

For each c in Range("DateList")
If c <> c.Offset(-1, 0) Then
Me.cBoxDates.AddItem Format(c, "m/d/yyyy")

Replace this line with
Me.cBoxDates.AddItem Format(c, "m/d/yyyy"), 0
This places the new item at the top of the list, automatically reversing the
order