Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting dates in a combobox
Dear all;
I have a list of dates in the format dd/mm/yyyy These dates are not unique. The list ( 2000 + items ) looks like : 29/06/2004 30/06/2004 29/06/2004 01/07/2004 30/06/2004 29/06/2004 01/07/2004 I want that list displayed in a combobox so I can choose from that list, but I only want to see the unique items. The list must be sorted in chronological order. It thus should look like : 29/06/2004 30/06/2004 01/07/2004 How do I do that with VBA ? Mark. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting dates in a combobox
Mark,
You need to filter the data and sort it before it hits the combo. Where is it a sourced from, a worksheet, a database? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mark Rosenkrantz" wrote in message ... Dear all; I have a list of dates in the format dd/mm/yyyy These dates are not unique. The list ( 2000 + items ) looks like : 29/06/2004 30/06/2004 29/06/2004 01/07/2004 30/06/2004 29/06/2004 01/07/2004 I want that list displayed in a combobox so I can choose from that list, but I only want to see the unique items. The list must be sorted in chronological order. It thus should look like : 29/06/2004 30/06/2004 01/07/2004 How do I do that with VBA ? Mark. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting dates in a combobox
Bob and other;
The sourcelist is on a worksheet. Mark. "Bob Phillips" wrote in message ... Mark, You need to filter the data and sort it before it hits the combo. Where is it a sourced from, a worksheet, a database? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mark Rosenkrantz" wrote in message ... Dear all; I have a list of dates in the format dd/mm/yyyy These dates are not unique. The list ( 2000 + items ) looks like : 29/06/2004 30/06/2004 29/06/2004 01/07/2004 30/06/2004 29/06/2004 01/07/2004 I want that list displayed in a combobox so I can choose from that list, but I only want to see the unique items. The list must be sorted in chronological order. It thus should look like : 29/06/2004 30/06/2004 01/07/2004 How do I do that with VBA ? Mark. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting dates in a combobox
Mark,
Here is some code thata ssumes that dates are in column 1, the combobox is a Forms combo called Drop Down 1 '----------------------------------------------------------------- Sub LoadUniqueToCombo() '----------------------------------------------------------------- Dim cRows As Long cRows = Cells(Rows.Count, "A").End(xlUp).Row Range("A1").Resize(cRows).Sort , KEY1:=Range("A1") Range("A1").EntireColumn.Insert Range("A1").Formula = "=COUNTIF($B$1:B1,B1)1" Range("A1").AutoFill Destination:=Range("A1").Resize(cRows) Range("A1").EntireRow.Insert Columns("A:A").AutoFilter Field:=1, Criteria1:="TRUE", Operator:=xlAnd With Range("B1:B" & cRows + 1) .SpecialCells(xlCellTypeVisible).EntireRow.Delete End With Columns("A:A").EntireColumn.Delete cRows = Cells(Rows.Count, "A").End(xlUp).Row With ActiveSheet.DropDowns("Drop Down 1") .ListFillRange = "$A$1:$A$" & cRows .DropDownLines = 8 .ListIndex = 1 End With End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mark Rosenkrantz" wrote in message ... Bob and other; The sourcelist is on a worksheet. Mark. "Bob Phillips" wrote in message ... Mark, You need to filter the data and sort it before it hits the combo. Where is it a sourced from, a worksheet, a database? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mark Rosenkrantz" wrote in message ... Dear all; I have a list of dates in the format dd/mm/yyyy These dates are not unique. The list ( 2000 + items ) looks like : 29/06/2004 30/06/2004 29/06/2004 01/07/2004 30/06/2004 29/06/2004 01/07/2004 I want that list displayed in a combobox so I can choose from that list, but I only want to see the unique items. The list must be sorted in chronological order. It thus should look like : 29/06/2004 30/06/2004 01/07/2004 How do I do that with VBA ? Mark. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting dates in a combobox
Bob;
Thanks. I'll try that one. Mark. "Bob Phillips" wrote in message ... Mark, Here is some code thata ssumes that dates are in column 1, the combobox is a Forms combo called Drop Down 1 '----------------------------------------------------------------- Sub LoadUniqueToCombo() '----------------------------------------------------------------- Dim cRows As Long cRows = Cells(Rows.Count, "A").End(xlUp).Row Range("A1").Resize(cRows).Sort , KEY1:=Range("A1") Range("A1").EntireColumn.Insert Range("A1").Formula = "=COUNTIF($B$1:B1,B1)1" Range("A1").AutoFill Destination:=Range("A1").Resize(cRows) Range("A1").EntireRow.Insert Columns("A:A").AutoFilter Field:=1, Criteria1:="TRUE", Operator:=xlAnd With Range("B1:B" & cRows + 1) .SpecialCells(xlCellTypeVisible).EntireRow.Delete End With Columns("A:A").EntireColumn.Delete cRows = Cells(Rows.Count, "A").End(xlUp).Row With ActiveSheet.DropDowns("Drop Down 1") .ListFillRange = "$A$1:$A$" & cRows .DropDownLines = 8 .ListIndex = 1 End With End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mark Rosenkrantz" wrote in message ... Bob and other; The sourcelist is on a worksheet. Mark. "Bob Phillips" wrote in message ... Mark, You need to filter the data and sort it before it hits the combo. Where is it a sourced from, a worksheet, a database? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mark Rosenkrantz" wrote in message ... Dear all; I have a list of dates in the format dd/mm/yyyy These dates are not unique. The list ( 2000 + items ) looks like : 29/06/2004 30/06/2004 29/06/2004 01/07/2004 30/06/2004 29/06/2004 01/07/2004 I want that list displayed in a combobox so I can choose from that list, but I only want to see the unique items. The list must be sorted in chronological order. It thus should look like : 29/06/2004 30/06/2004 01/07/2004 How do I do that with VBA ? Mark. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - How to get cell referrences from dates in combobox | Excel Discussion (Misc queries) | |||
format combobox in userform to accept dates | Excel Worksheet Functions | |||
Sorting Items in a ComboBox? | Excel Programming | |||
ComboBox with Dates | Excel Programming | |||
Sorting in a ComboBox | Excel Programming |