finding the original address of the data from a dropdown in vba

user_15223746 0
Viewed 151

Good day

I am looking for a way to find the address of the data used to create a dropdown in vba. The dropdown is created by using the code :

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="='" & ws.Name & "'!" & range1.Address

the dropdown code is used multiple time and I need to find a way to find the worksheet used for the formula1 code. This will help me be able to create cases that reference that worksheet later on in my code. please see picture attached:

Choosing the selected a or b value is then run by the code

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("J9:J15")) Is Nothing Then
Select Case Target
    Case "a": hala
    Case "b": toets_my_ws
End Select

End If

when I select one of the macros I need it to find the worksheet of the validated data.

enter image description here

vba excel
1 Answers

Edited again: I think this would be easier to integrate as a function - if you want to find the source range for your Target cell.

For example:

Dim rngSource As Range
'...
Set rngSource = ListSourceRange(Target)
If Not rngSource Is Nothing then
    rngSource.Parent.Activate  'do something with rngSource...
End If
'...

Function:

'Given a cell, see if it has a validation list, and
'  try to get the source range for the list
Function ListSourceRange(c As Range) As Range
    Dim vType, rng As Range
    On Error Resume Next       'ignore error if no validation
    vType = c.Validation.Type
    On Error GoTo 0            'stop ignoring errors
    
    If vType = 3 Then
        'try to get a source range...
        On Error Resume Next
        Set rng = Range(c.Validation.Formula1)
        On Error GoTo 0
    End If
    Set ListSourceRange = rng 'source range, or Nothing if no range found
End Function
user_4788840
Related