Obtaining the Index Number of a Drop-Down List

Question

In short, I am trying to get the index number of a drop-down list through VBA. In this case, the drop-down list is created via data validation.

Long story, I have created a simple worksheet that contains a few drop-down lists that were made through data validation, which contains strings only.

Depending on the user selection, I would like to then perform some tasks depending on the selected option. Instead of inputting the entire string into a select case or an if-else then statement, getting the index number of the selected option of that particular drop-down list would be a much more efficient way of doing it.

I am aware of the ListIndex property which function fits my exact requirement, but as far as I'm aware, it is only applicable to listbox and combobox.

I am also aware of another similar question raised: Get position (in number) of selected item in dropdown list. This solution however does not work for me and results in a returned value of "0" in most cases when selecting options other than the first item in the drop down list. From my shallow understanding in VBA, it seems to only function as expected when there are no repeating words between the various drop-down options.

I would also greatly appreciate it if anyone could elaborate on the solution code as I would like to understand what exactly is going on and grow my knowledge on VBA programming.

Answer 1

This works fine for me - similar to Doug's answer at your linked post.

Sub tester()

    Debug.Print SelectedListIndex([F1])

End Sub



'If `c` has a list-based validation (from direct entry, not a range reference)
'  and a value has been selected, return the index of the value in the list
Function SelectedListIndex(c As Range)
    Dim arr, m, v
    m = -1
    v = c.Value
    If Len(v) > 0 Then
        If Not c.Validation Is Nothing Then
            If c.Validation.Type = xlValidateList Then
                arr = Split(c.Validation.Formula1, _
                            Application.International(xlListSeparator))
                m = Application.Match(c.Value, arr, 0)
            End If
        End If
    End If
    SelectedListIndex = IIf(IsError(m), -1, m)
End Function