Tom Ogilvy
12/15/2006 3:24:00 AM
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" <martin_fishlock@yahoo.co.uk.cutthis> wrote in message
news:3169D452-4DB3-48A6-ADC3-98790EF2F307@microsoft.com...
> 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.
>
>
> "bartvandongen@gmail.com" 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
>>
>>