Alphabetized Data Validation Dropdowns

Situation

When setting up data validation for a cell, or more often a column of cells, I often use the List option with drop-down using the data in a range/column in another worksheet.  Most often these columns of data are fairly static so they get ordered only when I'm doing development or maintenance.

Problem

There are occasions when the data is moved around, i.e. resorted, or the right way to present it on another worksheet is not in the order needed for the drop-down.  I simply needed to sort what was in the source area before presenting it in the drop-down.

Step One: Sort Function

I knew I needed to sort a list of names so I wrote a sort function  put them in alphabetical order in an array.  Easy cheesy.

Now the hard part: Transfer the array to the DataValidation.

Failures

I mentioned that I use ranges for dropdowns.  That's only partially true.  I use ranges referred to by Names.

I have seen where Functions can be placed in a Name, so I tried putting the function in a Name and then refer to the Name in the data validation dialogue.  NoGo.

The second attempt was to put the function directly into the data validation dialogue.  NoGo.

Revised the function to return a string consisting of each name separated by  a comma, just like if I were putting it in manually for a "Yes,No" data validation then tried it in the Name/Function and in the Data Validation directly.  NoGo*2

Solution

A bit of research got me into the Validation object.  Now life was easy.  I simply took the from the array and put it into Formula1

The Function

Function SortedCellValues(ByRef rng As Range)
    Dim v()
    ReDim v(1 To rng.Cells.Count)
    Dim TempV
    Dim cel As Range
    Dim i As Double
    Dim j As Double
   
    For i = LBound(v) To UBound(v)
        v(i) = rng.Cells(i)
    Next i
   
    For i = LBound(v) To UBound(v) - 1
        For j = i + 1 To UBound(v)
            If v(i) > v(j) Then
                TempV = v(j)
                v(j) = v(i)
                v(i) = TempV
            End If
        Next j
    Next i
    For i = LBound(v) To UBound(v)
        SortedCellValues = SortedCellValues + v(i)
        If i < UBound(v) Then
            SortedCellValues = SortedCellValues + ","
        End If
    Next i
End Function 

Trigger  and the Silver Bullet

Nope.  This is not an episode of the Lone Ranger.  This refers to putting the string from the function into the the right place and when to execute the update of the drop-down values.

Private Sub Worksheet_Activate()

    Dim cel As Range

    Dim strDropDownValues As String

    Dim ValDatRange As Range

    Set ValDatRange = Me.Range("valRange") ' This is named in the workbook

    strDropDownValues = SortedCellValues(ThisWorkbook.Names("lstnamesraw").RefersToRange)

    For Each cel In ValDatRange.Cells

        cel.Validation.Modify Formula1:=strDropDownValues

    Next cel

end sub