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

demorial

New Member
Joined
Feb 19, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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:
 

Attachments

  • adress_finder.png
    adress_finder.png
    6.6 KB · Views: 2

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Like this, where dvCell is the data validation dropdown cell.
VBA Code:
    Dim dvCell As Range, dvCells As Range
    
    Set dvCell = Worksheets("Sheet2").Range("A2")    
    Set dvCells = Evaluate(dvCell.Validation.Formula1)

    Debug.Print "Data validation cell: " & dvCell.Address
    Debug.Print "Source sheet: " & dvCells.Worksheet.Name
    Debug.Print "Source cells: " & dvCells.Address
 
Upvote 0
I got a solution that works for my needs further on but thank you for your response

my solution is:
VBA Code:
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
 
Last edited by a moderator:
Upvote 0
Solution

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top