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

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

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

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