VBA - Data Validation - Identify cell selected validated item comes from

[maximilianshade]

I have a rather odd question that I hope someone can help me with.

For this scenario, please assume the following:

There is a named range 'Countries', that contains cells 'A1:A10' on sheet 'Validation Tables'.

There is a cell, B1, on a different sheet, that is using Data Validation to provide a drop down list limited to the items in the named range 'Countries'.

In this example, assume a user selects the drop down list, and selects 'USA', which is located specifically on cell 'A4' on sheet 'Validation Tables'

What I need to know is, via VBA, during an event which is firing on B1 changing, is there any way to KNOW which cell (in this case A4) corresponds to the selected item in the drop down list?

 

[VoG]

Odd indeed

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
If Target.Address(False, False) = "B1" Then
    Set r = Sheets("Validation Tables").Columns("A").Find(what:=Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    MsgBox r.Address(False, False)
End If
End Sub

 

[maximilianshade]

Thank you, that's a very good start towards what I need.
I provided a specific example, but really, I need a general way of doing this, so unfortunatley, this leaves me with a few more related questions.

Assume I can't hardcode 'Column A' for the find portion, because in this now more general case, this may be being used for validation tables that could be found in any column.

Assume also that elsewhere on the Validation Tables sheet, in another validation table, is the same data value of 'USA', maybe in the same column, maybe not.

1. Is this possible without hardcoding the column?

2. Is there a way of adjusting this example so that it restricts its search to the named range that is being used for data validation (so as to prevent it hitting the same value in a different table in the same column) ?

3. If the answer to 2 is 'No', then is there a way of adjusting this example so that it figures out the column that the data validation named range is in, and uses that as the column to search?

 

[VoG]

#2 is easiest here. Replace Columns A with Range("Your named Range").

 

[maximilianshade]

Thank you VoG. That was sufficient enough of a push to get me to what I needed. Enclosed is my final code framework, on the chance that someone else ever needs something similar to what I needed.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ret As Excel.Range

Set ret = CheckForValidationCell(Target)
If Not ret Is Nothing Then
    ' Do here whatever you would do if you found the cell you needed
    MsgBox "Woo!"
Else
    ' We didn't find anything, so, do nothing.
    MsgBox "Aww!"
End If

End Sub

Private Function CheckForValidationCell(currentCell As Range)

Dim testForValidation As Range
Dim currentValidation As Excel.Validation
Dim validationRange As Excel.Range
Dim validationType As Excel.XlDVType

' Check if currentCell contains Data Validation
On Error Resume Next
Set testForValidation = Intersect(currentCell, Cells.SpecialCells(xlCellTypeAllValidation))
On Error GoTo 0

If Not testForValidation Is Nothing Then
    ' Check for validation type
    Set currentValidation = testForValidation.Validation
    On Error Resume Next
        validationType = currentValidation.Type
        If Err.Number <> 0 Then
            Exit Function
        End If
    On Error GoTo 0

    ' Formulas (a named range) only used in List and Custom types
    If (validationType = xlValidateList Or validationType = xlValidateCustom) Then
    
        ' test for range reference and not a list of values
        On Error Resume Next
            Set validationRange = Excel.Range(currentValidation.Formula1)
        On Error GoTo 0
    
        If Not validationRange Is Nothing Then
            ' We have a named range.  Obtain the cell that corresponds to the value selected.
            Set currentCell = validationRange.Find(what:=currentCell.Value, LookIn:=xlValues, lookat:=xlWhole)
            MsgBox currentCell.Address(False, False)
            Set CheckForValidationCell = currentCell
        End If
    End If
Else
    ' The currentCell does NOT contain data validation, so we don't care
    Set CheckForValidationCell = Nothing
End If
End Function