Combo Box question
in [Programming]
|
From: Sam on 26 Apr 2010 20:14 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
From: Dave Peterson on 26 Apr 2010 21:13 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
From: FSt1 on 26 Apr 2010 21:31 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
From: FSt1 on 26 Apr 2010 22:44 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
From: Sam on 27 Apr 2010 12:07 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
|
Next
|
Last
Pages: 1 2 Prev: moved macros to new book, now not working Next: Search multiple columns |