I have a moving list which is the source of validation. I estimated that I
would be using 120 items which are not yet all known. When a new item is
entered, I need to re-sort the list alphabetically so that the names that
will appear on the drop down box of the validation would be alphabetized. Is
there any method by which the names in the list and/or in the dropdown box
would be automatically alphabetized as new items are entered in the list?
Anyone, please help me.

for demonstration, assume you data starts in cell M10 of Sheet1

first create a dynamic named range so only the populated cells will be in
your list.

Insert=Name=Define
name: List1
refersto: =Offset(Sheet1!$M$10,0,0,countA(Sheet1!$M$10:$M$16 0),1)

Use List1 as the source for your validation dropdown

=List1

in the Change Event of the worksheet (on the sheet tab, right click, select
view code. In the resulting module at the top, in the left dropdown, select
worksheet; in the right dropdown, select Change) do your sort:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Not Intersect(Target, Range("M10:M160")) _
Is Nothing Then
Application.EnableEvents = False
Range("List1").Sort key1:=Range("List1")(1), _
order1:=xlAscending
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"Glice" wrote:

I have a moving list which is the source of validation. I estimated that I
would be using 120 items which are not yet all known. When a new item is
entered, I need to re-sort the list alphabetically so that the names that
will appear on the drop down box of the validation would be alphabetized. Is
there any method by which the names in the list and/or in the dropdown box
would be automatically alphabetized as new items are entered in the list?
Anyone, please help me.

Let's say you are managing your list in column J and that you have settup
your validation by:

Data Validation allow List and point to column J

Then in worksheet code enter:

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("J:J"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Columns("J:J").Select
Selection.Sort Key1:=Range("J1"), Order1:=xlAscending
Application.EnableEvents = True
End Sub


This code will detect entries in column J and automatically re-sort the
column.


Your validation list will always be in alphabetic order.

Just remember that the macro must go in worksheet code.
--
Gary's Student


"Glice" wrote:

I have a moving list which is the source of validation. I estimated that I
would be using 120 items which are not yet all known. When a new item is
entered, I need to re-sort the list alphabetically so that the names that
will appear on the drop down box of the validation would be alphabetized. Is
there any method by which the names in the list and/or in the dropdown box
would be automatically alphabetized as new items are entered in the list?
Anyone, please help me.