How to have data ordered via Data validation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bartvandongen@gmail.com - 15 Dec 2006 01:00 GMT

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 to this Message

Martin Fishlock - 15 Dec 2006 02:53 GMT

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

Signature

Hope this helps
Martin Fishlock
Please do not forget to rate this reply.

> I have tries to find it in the Help section as well as in this
> newsgroup, but without success.
[quoted text clipped - 10 lines]
>
> Bart

Reply to this Message

Tom Ogilvy - 15 Dec 2006 03:24 GMT

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.

Signature

Regards,
Tom Ogilvy

> Bart,
>
[quoted text clipped - 34 lines]
>>
>> Bart

Reply to this Message

Martin Fishlock - 15 Dec 2006 08:57 GMT

Thanks Tom for point out that omission.

Signature

Hope this helps
Martin Fishlock
Please do not forget to rate this reply.

> The code posted by Martin causes an error if a change is made outside column
> 1.
[quoted text clipped - 51 lines]
> >>
> >> Bart

Reply to this Message