Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to have data ordered via Data validation
I have tries to find it in the Help section as well as in this
newsgroup, but without success. I have a column that fill itself with data from countif formula. ok, so far. That column is Insert Name Named a list. No data validation at present. And I would like to have this column auto -ordered, so when the data (numbers) can be choosen via the combo, it is ordered. Bart |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to have data ordered via Data validation
Bart,
One solution is to use a worksheet change event to pick up changes to the list column. I have assumed that the list is in column A and that there are no headings and the data validation combo list box is in C1. You need to place this in the worksheet code of the workbook where the list is and what it does is sort the list after any change to column A. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns(1)) Then Columns("A:A").Sort Key1:=Range("A1"), _ Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub -- Hope this helps Martin Fishlock Please do not forget to rate this reply. " wrote: I have tries to find it in the Help section as well as in this newsgroup, but without success. I have a column that fill itself with data from countif formula. ok, so far. That column is Insert Name Named a list. No data validation at present. And I would like to have this column auto -ordered, so when the data (numbers) can be choosen via the combo, it is ordered. Bart |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to have data ordered via Data validation
The code posted by Martin causes an error if a change is made outside column
1. Private Sub Worksheet_Change(ByVal Target As Range) If not Intersect(Target, Columns(1)) is nothing Then Columns("A:A").Sort Key1:=Range("A1"), _ Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End If End Sub Should avoid the error. -- Regards, Tom Ogilvy "Martin Fishlock" wrote in message ... Bart, One solution is to use a worksheet change event to pick up changes to the list column. I have assumed that the list is in column A and that there are no headings and the data validation combo list box is in C1. You need to place this in the worksheet code of the workbook where the list is and what it does is sort the list after any change to column A. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns(1)) Then Columns("A:A").Sort Key1:=Range("A1"), _ Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub -- Hope this helps Martin Fishlock Please do not forget to rate this reply. " wrote: I have tries to find it in the Help section as well as in this newsgroup, but without success. I have a column that fill itself with data from countif formula. ok, so far. That column is Insert Name Named a list. No data validation at present. And I would like to have this column auto -ordered, so when the data (numbers) can be choosen via the combo, it is ordered. Bart |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to have data ordered via Data validation
Thanks Tom for point out that omission.
-- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Tom Ogilvy" wrote: The code posted by Martin causes an error if a change is made outside column 1. Private Sub Worksheet_Change(ByVal Target As Range) If not Intersect(Target, Columns(1)) is nothing Then Columns("A:A").Sort Key1:=Range("A1"), _ Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End If End Sub Should avoid the error. -- Regards, Tom Ogilvy "Martin Fishlock" wrote in message ... Bart, One solution is to use a worksheet change event to pick up changes to the list column. I have assumed that the list is in column A and that there are no headings and the data validation combo list box is in C1. You need to place this in the worksheet code of the workbook where the list is and what it does is sort the list after any change to column A. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns(1)) Then Columns("A:A").Sort Key1:=Range("A1"), _ Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub -- Hope this helps Martin Fishlock Please do not forget to rate this reply. " wrote: I have tries to find it in the Help section as well as in this newsgroup, but without success. I have a column that fill itself with data from countif formula. ok, so far. That column is Insert Name Named a list. No data validation at present. And I would like to have this column auto -ordered, so when the data (numbers) can be choosen via the combo, it is ordered. Bart |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom data validation on cells with data validation values | Excel Worksheet Functions | |||
Data Validation to range names for Chart Source Data | Charts and Charting in Excel | |||
Data Validation vs VLOOKUP - Linking to data in a seperate file | Excel Worksheet Functions | |||
Importing ordered data on datasheet | New Users to Excel | |||
extract data from a random list & place in another ordered list | Excel Discussion (Misc queries) |