Finding External Links in Data Validation

I kept getting the Edit Links message when I opened this on particular file, but I could never find the link. It wasn’t in any formulas or range names. I ended up copying each cell to a new worksheet and seeing when that new worksheet got the link. Fortunately it was about the 10th cell that I copied. I figured out that the data validation list referred to a named range. I’m pretty sure that the named range still exists in whichever workbook this sheet was copied from. I’d rather not discuss how long it took me to find the source of this problem, so I’ll just post the code I used to find all its cousins.

Sub FindLinksInValidation()
   
    Dim rCell As Range
    Dim sDvForm As String
       
    For Each rCell In ActiveSheet.UsedRange.Cells
        ‘Store the Formula1 property if there is one
       On Error Resume Next
            sDvForm = “”
            sDvForm = rCell.Validation.Formula1
        On Error GoTo 0
       
        ‘If Formula1 has a bracket, it’s a good candidate
       ‘for containing an external link
       If InStr(1, sDvForm, “]”) > 0 Then
            Debug.Print rCell.Address, rCell.Validation.Formula1
        End If
    Next rCell
   
End Sub
Posted in Uncategorized

18 thoughts on “Finding External Links in Data Validation

  1. FindLink did not work for me. The code was password protected so I couldn’t see if it looked in data validation or not. That was the second thing I tried.

    Edit – Find did not work for me – it was the first thing I tried. Is it supposed to work in data validation? It didn’t or I was doing something wrong.

  2. It takes some work to use external references in data validation, so it may be karma that it takes some work to find such external references. Or maybe it a paraphrase of one of life’s rules: no bit of cleverness goes unpunished.

  3. Flexfind worked great for a non-programmer. thanks. Tried to make a contribution to the developer through their site and their Paypal account but was told there was an error. Sorry

  4. Actually fzz, it is VERY easy to create an external link for data validation… copy the cells with the data validation into another workbork and voila… you have a file that uses an external file for data validation… a real pain to find and resolve these.

    This macro did not work for me by the way…. no results but I still get an error message when I open the file.

  5. Follow-up… I found my links… there were embedded in conditional formatting rules…

    So now I’ve added this to my checklist:
    1. Search Formulas with Find for links to external files
    2. Search Range Names for ranges in external files.
    3. Search Data Validation rules for every cell to see if any of them are sourced from a list in an external file.
    4. Search Conditional Formatting rules to see if any of them are conidtional and cells in external files.

    If only there was a single place in Excel to find, edit, and break external links….

  6. OK, this is a thread since long time back, but it really helped me out. never thought of looking into conditional formatting! Great!

  7. Buttons and combo boxes can also contain references to macros and control cells in other worksheets.

  8. This is an old thread, but also a topic that has caused me a lot of pain, so I hope this helps someone. I modified Dick Kusleika’s code above slightly. This variation will unhide every worksheet in your file, create a new worksheet called ‘external links’, and then list all the worksheets and cells where it finds a link within the data validation in the ‘external links’ worksheet. It’s a bit slow, but faster than digging through the file manually.

    Sub FindLinksInValidationWorkbook()
    
    Dim rCell As Range
    Dim sDvForm As String
    Dim counter As Integer
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
    'creates a worksheet called external links
    Set ws = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "external links"
    Sheets("external links").Cells(1, 1).value = "cell address"
    Sheets("external links").Cells(1, 2).value = "worksheet"
    
    
    'unhide all worksheets
    For Each ws In Worksheets
        ws.Visible = True
    Next
    
    'set a counter
    counter = 2
    
    'loop through all the worksheets and find the offending references
    For Each ws In Worksheets
        ws.Select
        Cells.Select
    
        For Each rCell In ActiveSheet.UsedRange.Cells
            'Store the Formula1 property if there is one
            On Error Resume Next
                sDvForm = ""
                sDvForm = rCell.Validation.Formula1
            On Error GoTo 0
    
            'If Formula1 has a bracket, it’s a good candidate
            'for containing an external link
            If InStr(1, sDvForm, "]") > 0 Then
                Sheets("external links").Cells(counter, 1).value = rCell.Address
                Sheets("external links").Cells(counter, 2).value = ActiveSheet.Name
    
                'this gets the formula and the external file, but i don't need it. uncomment it if you find it useful
                'Sheets("external links").Cells(counter, 3).value = rCell.Validation.Formula1
    
                counter = counter + 1
    
            End If
    
            'selects one cell in the worksheet after searching through all the cells
            Range("A1").Select
        Next rCell
    
    Next
    
    Application.ScreenUpdating = True
    

    End Sub

  9. On the hunt again and found this thread again, nice add Peter but one typo came through, your > shows as &gt ;

      If InStr(1, sDvForm, "]") > 0 Then
    

    should be

      If InStr(1, sDvForm, "]") > 0 Then
    

    Thanks!

  10. Thanks for the comment Howard. I don’t know why this site can’t render HTML appropriately, but I’m working on a fix.

  11. To everyone that has contributed on this thread and in particular to Dick for creating the post and Peter for enhancing the script, THANK YOU. I’ve had a workbook that has been troubling me with this issue for way too long and now it’s resolved!

  12. How can Peter Malczyk’s code be amended to also find links within conditional formatting?

  13. Hi all

    As usually, a very helpful piece of code. It just saved my day – actually two of them, when I was struggling to remove the external links error message.
    I did a minor amendment to get the formula put in as well:
    1) Adding:
    Sheets(“External links”).Cells(1, 3).Value = “Formula”
    2) Replacing the uncommented output line by
    Sheets(“external links”).Cells(counter, 3).Value = “””” & rCell.Validation.Formula1 & “”””
    This will put the formula as text string into it instead of evaluating it – giving an error. The double quote formatting is for the European theatre.

Leave a Reply

Your email address will not be published. Required fields are marked *