Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / March 2006

Tip: Looking for answers? Try searching our database.

I want to automatically alphabetize my list for my validation.

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Glice - 20 Mar 2006 17:28 GMT
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.
Tom Ogilvy - 20 Mar 2006 18:04 GMT
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$160),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

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 3 lines]
> would be automatically alphabetized as new items are entered in the list?
> Anyone, please help me.
Gary''s Student - 20 Mar 2006 18:17 GMT
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.
Signature

Gary's Student

> 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
[quoted text clipped - 3 lines]
> would be automatically alphabetized as new items are entered in the list?
> Anyone, please help me.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.