Auto sort drop-down list

[BRISBANEBOB]

Is it possible to autosort a drop-down list based on the number of times an item is selected? A drop-down might have 500 lines so it is user-friendly if the top lines are those selected by users the most often.

Any ideas?

[JBeaucaire]

Here's a sample worksheet_change macro to do this, only way I could think of. Basically, a "count" column next to your validation list makes a simple sort on the names/values do the work.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRow As Long
If Target.Count > 1 Or Target = "" Then Exit Sub
    If Not Intersect(Target, Range("G:G")) Is Nothing Then
    Application.EnableEvents = False
    
        MyRow = WorksheetFunction.Match(Target, Range("Names"), 0)
        Cells(MyRow, "B") = Cells(MyRow, "B") + 1
        Range("NameSort").Sort Key1:=Range("B1"), Order1:=xlDescending, Key2:=Range("A1"), Order2:=xlAscending
    
    Application.EnableEvents = True
    End If
End Sub

Sample attached.

[BRISBANEBOB]

A rocket scientist, indeed.

Thanks

[martindwilson]

had to sleep after reading post last night but my idea was this

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim mymatch As Integer
Dim myrange As String
    If Target.Address <> "$A$1" Then Exit Sub
    mymatch = Application.WorksheetFunction.Match(Range("a1"), Range("h1:h500"), 0)

myrange = "g" & mymatch
Range(myrange) = Range(myrange) + 1
 Columns("G:H").Sort Key1:=Range("G1"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        
Application.ScreenUpdating = True
End Sub

wher h1:50 contains the validation list and g is an empty column
somewhat similar to jb's tho

[BRISBANEBOB]

Thanks for that - an interesting solution.

Much appreciated