New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

ListIndex of Cell Value in Validation List

Free Excel Macros

ListIndex of Cell Value in Validation List

Postby Sisyphus » Tue Feb 21, 2012 1:07 am

This function returns the sequential number of a value in a validation list. If you select the 3rd item in the list it will return 3, select the 5th item in the list and the function returns 5, etc.
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
Application.Volatile
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:
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.
You do not have the required permissions to view the files attached to this post.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 22 guests