- Code: Select all
Public Function ValListIndex(ByVal Target As String) As Long
' Return the ListIndex of the value in Target
' Created by Sisyphus @ ExcelKey von Feb 21, 2012
Dim Cell As Range
Dim ValList As String
Dim ValItem() As String
Dim LiSep As String
Dim i As Integer
Application.Volatile
On Error GoTo ErrExit
Set Cell = ActiveSheet.Range(Application.ConvertFormula _
(Target, xlA1, xlA1, xlAbsolute)). _
Cells(1)
ValList = Cell.Validation.Formula1
If Len(ValList) = 0 Then GoTo ErrExit
LiSep = Application.International(xlListSeparator)
ValItem = Split(ValList, LiSep)
For i = UBound(ValItem) To 0 Step -1
If i >= LBound(ValItem) Then
If ValItem(i) = Cell.Value Then Exit For
End If
Next i
ValListIndex = i + 1
Exit Function
ErrExit:
ValListIndex = -1
End Function
If the referenced cell is blank the function returns 0 (zero), and if the cell doesn't have a validation list the returned value is -1. It is designed to be deployed as a worksheet UDF or in VBA projects. In either environment it replaces the need for endless nested IFs, such as IF(E4 = "Jan", 1(IF(E4 = "Feb", 2(IF(E4 = "Mar" etc.etc.
If the function is to be called from a worksheet it may be copied from above and pasted directly into a normal Code Module ('Module1' by default). If deployed within a VBA project changing the declaration to 'Private' is recommended while it is called from the same code sheet where it resides. Also, the code line
should be removed as its instruction refers to calls from the worksheet. In either case the function is ready for use immediately. It needs one argument when called which is the address of the cell where the validation list is. The validation list must be entered in the validation cell itself; it can't be a named range. Use this formula to call the function from a worksheet cell:Application.Volatile
- Code: Select all
= ValListIndex("$E$3") ' E3 is the cell of the validation Dropdown
The attached workbook has an example of how the function can be used.