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: 1

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,703
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
 

demorial

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

Watch MrExcel Video

Forum statistics

Threads
1,126,928
Messages
5,621,639
Members
415,849
Latest member
PhoenixRising2015

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
Top