Get the position (in quantity) of the selected item in the drop-down list

Question

In the drop-down list, I have several items. Can I, when I select an item, get the position of this item in the list as a number?

Answer 1

If you use a list or combo box, ListIndex will look like you.

VB Help for ListIndex : ListIndex or sets the index number of the currently selected item in a list or combo box. Read / Write Long. Remarks. You cannot use this property with multiselect list fields.

If nothing is selected, the ListIndex value is -1 . If memory is used, this is a zero-based index.

ListIndex cannot be installed at design time, so it is not listed in the properties window.

When entering the code, enter the name of the list field, then click the button and the editor will display all available properties. Scroll down the list, pay attention to any interesting interest, then look at them.

Answer 2

If you are looking for a data validation list index, this is what I would do:

Put the following code in the ThisWorkbook module:

 Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim ValidationIndex As Long Dim rngTest As Excel.Range 'assumes the data validation is in a cell named "rngTest" On Error Resume Next Set rngTest = Sh.Range("rngTest") If rngTest Is Nothing Then Exit Sub End If On Error GoTo 0 If Not Intersect(ActiveCell, Sh.Range("rngTest")) Is Nothing Then ValidationIndex = GetValidationIndex MsgBox ValidationIndex End If End Sub 

Put this function in the ThisWorkbook module as well or in any regular module:

 Function GetValidationIndex() As Long 'returns a 1-based index Dim rngTest As Excel.Range Dim varValidationString As Variant Dim ErrNumber As Long Dim i As Long With ActiveCell.Validation If .Type = xlValidateList Then '3 On Error Resume Next Set rngTest = ActiveCell.Parent.Range(.Formula1) 'I do this goofy thing with ErrNumber to keep my indenting and flow pretty ErrNumber = Err.Number On Error GoTo 0 'if the Validation is defined as a range If ErrNumber = 0 Then GetValidationIndex = Application.WorksheetFunction.Match(ActiveCell.Value2, rngTest, 0) Exit Function 'if the validation is defined by comma-separated values Else varValidationString = Split(.Formula1, ",") For i = LBound(varValidationString) To UBound(varValidationString) If varValidationString(i) = ActiveCell.Value2 Then GetValidationIndex = i + 1 Exit Function End If Next i End If End If End With End Function 

Answer 3

I think there is no need to use a function. You can get it using only the Match function, as in Doug's answer.

 Dim GetValidationIndex as Integer Dim rngTest as Range ' Get the validation list With ActiveCell.Validation Set rngTest = ActiveCell.Parent.Range(.Formula1) end with GetValidationIndex = Application.WorksheetFunction.Match(ActiveCell.Value2, rngTest, 0)