## 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