Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box question
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. Sam wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box question
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 "Sam" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box question
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 "FSt1" wrote: 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 "Sam" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box question
Thanks a lot for your help. This is exactly what I need.
Sam "FSt1" wrote: 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 "FSt1" wrote: 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 "Sam" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box question
Sam,
there is a simple solution, add the item bevore all other items in the list. See the changed code below. Helmut. "Sam" schrieb im Newsbeitrag ... 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo Box Question | Excel Programming | |||
Combo Box if... then... else question | Excel Programming | |||
combo box question | Excel Programming | |||
Combo box question | Excel Programming | |||
Combo Box Question | Excel Programming |