1

I have a drop down list generated in Excel from row values. I want to select from that drop down, get the index of that value in the drop down array, then use that for an offset value to select the value from a different column.

I can't seem to find a simple way to get the index of the selected value in the drop down.

Every answer I find makes assumptions about where the array is generated, but here they are generated by different sets of data that can be changed at any time.

3 Answers 3

2

you don't need VBA to resolve this.

  1. Define a named range in the workbook for the list you are using for the validation (In my case I have <5, >5 and >10 in cells A2,A3 & A4 for Years of Service, so the named range is called Years_of_service.

  2. Validation is in cell C1 using the defined name range of Years_of_service for the list.

  3. Formula in cell D1 to work out the offset or the item selected in the list is =MATCH(C1,Years_of_service,0) .

Mark O'Rafferty

1

Updated

Try pasting the following VBA into "ThisWorkbook" (If you need help with this, ask in comment):

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
    Sheets("Sheet1").Range("E2").Value = ValidationIndex
End If
End Sub

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

A few notes:

  • This assumes that your drop-down box/cell is named "rngTest". If not, then rename it or change it throughout this code to it's existing name.
  • Line 15 defines the exact cell where the index result will output. You'll need to change the sheet and cell names to match your own.

I have tested this and it does work, though I cannot take full credit as the original code was something I found months ago. If you have trouble, it is sometimes easier to replicate this process in a new worksheet until you understand it's theory of operation.

2
  • Thanks Robert. I actually want to use the index combined with some data and display it in another cell, so I don't think a msgbox will work... sounds like the only option I have is turning this into a combobox?
    – user339389
    Jun 30, 2014 at 7:47
  • The code has been updated for you and will now output to a cell that can then be used however you'd like. Jun 30, 2014 at 12:22
0

The function "GetValidationIndex" is good. However, for some regional settings the line "varValidationString = Split(.Formula1, ",")" is not valid, because the character used to separate the different values is ";".

1
  • I suggest use: :listValueArray = Split(.Formula1, Application.International(xlListSeparator)) Mar 26, 2021 at 9:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .