How to get the name of a range referenced by a drop down list in excel VBA

0
votes

I have a worksheet event where a userform is triggered whenever a drop down list is selected. The drop down list is in sheet1 while the references is using a named range in sheet2. Now I need that worksheet event to only trigger my userform for drop down lists that are referenced to a specific named range. I plan to use...

If Not Intersect(Target, Range("NAMED_RANGE_1")) Is Nothing Then
  //do not show userform, be happy!
else
  //show userform, you are awesome!

But I can't get the referenced named range of the drop down list selected. Can anyone instruct me how to get the named range of the selected drop down list?

Thanks!

1
excelvba
Make sure the NAMED_RANGE_1 is in the same worksheet as Target. Otherwise they cannot intersect. – Pᴇʜ

1 Answers

1
votes

You can get the reference list by the following way (I assume you are using Worksheet_SelectionChange(...) function):

On Error GoTo endSub   ' skip if no validation set for Target
If Target.Validation.Formula1 <> "=NAMED_RANGE_1" Then
    ' do not show userform, be happy!
Else
    ' show userform, you are awesome!
End If

endSub:
    Exit Sub