21 thoughts on “Finding External Links in Data Validation”

1. Of course Bill Manville’s Findlink would have speeded up the find process quite a bit:
   http://www.oaltd.co.uk/mvp

2. Why not Edit – Find – Find: “]” – Find all ?

3. 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.

4. Flexfind would have located them without a problem, make sure you check “Objects” and look in formulas.

5. 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.

6. 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

7. 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.

8. 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….

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

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

11. 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.

    Visual Basic

    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

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

    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

12. On the hunt again and found this thread again, nice add Peter. Thanks!

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

    Visual Basic

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

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

    should be

    Visual Basic

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

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

    Thanks!

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

15. 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!

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

17. 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.

18. thank you Peter Malczyk

19. Hi,
    Thanks a lot for the code, very helpful.
    I did some very minor improvement.
    1/ I don’t think it is necessary to unhide the worksheet (tested on XL 2016, even with xlVeryHidden)
    2/ I store the result in an array before sending to the worksheet ( a bit faster for lost of cells).
    3/ I used activeworkbook instead of thisworkbook

    Visual Basic

    Option Explicit Sub FindExternalLinksValidation() Dim rgeCell As Range Dim sDvForm As String Dim counter As Integer Dim wksResult As Worksheet Dim wks As Worksheet Dim arrWorksheetName As Variant Dim arrCellAddress As Variant Dim arrFormula As Variant ReDim arrWorksheetName(1 To 1) As Variant ReDim arrCellAddress(1 To 1) As Variant ReDim arrFormula(1 To 1) As Variant Dim arrResult As Variant Dim lRow As Long Dim lRowResult As Long Application.ScreenUpdating = False lRow = 1 For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible For Each rgeCell In wks.UsedRange.Cells On Error Resume Next sDvForm = "" sDvForm = rgeCell.Validation.Formula1 On Error GoTo 0 If InStr(1, sDvForm, ".xl") > 0 Then 'or we could use [ lRow = lRow + 1 ReDim Preserve arrWorksheetName(1 To lRow) As Variant ReDim Preserve arrCellAddress(1 To lRow) As Variant ReDim Preserve arrFormula(1 To lRow) As Variant arrWorksheetName(lRow) = wks.Name arrCellAddress(lRow) = rgeCell.Address arrFormula(lRow) = "'" & sDvForm End If Next rgeCell Next wks If lRow 0 Then Set wksResult = ActiveWorkbook.Sheets.Add(before:=ThisWorkbook.Sheets(1)) ReDim arrResult(1 To lRow, 1 To 3) As Variant arrResult(1, 1) = "Worksheet Name" arrResult(1, 2) = "Address" arrResult(1, 3) = "Formula" For lRowResult = 2 To UBound(arrWorksheetName, 1) arrResult(lRowResult, 1) = arrWorksheetName(lRowResult) arrResult(lRowResult, 2) = arrCellAddress(lRowResult) arrResult(lRowResult, 3) = arrFormula(lRowResult) Next lRowResult wksResult.Range("A1:C" & UBound(arrResult, 1)).Value = arrResult End If Application.ScreenUpdating = True End Sub

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

    Option Explicit   Sub FindExternalLinksValidation()     Dim rgeCell As Range     Dim sDvForm As String     Dim counter As Integer     Dim wksResult As Worksheet     Dim wks As Worksheet          Dim arrWorksheetName As Variant     Dim arrCellAddress As Variant     Dim arrFormula As Variant     ReDim arrWorksheetName(1 To 1) As Variant     ReDim arrCellAddress(1 To 1) As Variant     ReDim arrFormula(1 To 1) As Variant          Dim arrResult As Variant          Dim lRow As Long     Dim lRowResult As Long          Application.ScreenUpdating = False     lRow = 1     For Each wks In ActiveWorkbook.Worksheets         wks.Visible = xlSheetVisible         For Each rgeCell In wks.UsedRange.Cells             On Error Resume Next             sDvForm = ""             sDvForm = rgeCell.Validation.Formula1             On Error GoTo 0             If InStr(1, sDvForm, ".xl") > 0 Then 'or we could use [                 lRow = lRow + 1                                  ReDim Preserve arrWorksheetName(1 To lRow) As Variant                 ReDim Preserve arrCellAddress(1 To lRow) As Variant                 ReDim Preserve arrFormula(1 To lRow) As Variant                                  arrWorksheetName(lRow) = wks.Name                 arrCellAddress(lRow) = rgeCell.Address                 arrFormula(lRow) = "'" & sDvForm             End If         Next rgeCell     Next wks          If lRow  0 Then         Set wksResult = ActiveWorkbook.Sheets.Add(before:=ThisWorkbook.Sheets(1))         ReDim arrResult(1 To lRow, 1 To 3) As Variant         arrResult(1, 1) = "Worksheet Name"         arrResult(1, 2) = "Address"         arrResult(1, 3) = "Formula"         For lRowResult = 2 To UBound(arrWorksheetName, 1)             arrResult(lRowResult, 1) = arrWorksheetName(lRowResult)             arrResult(lRowResult, 2) = arrCellAddress(lRowResult)             arrResult(lRowResult, 3) = arrFormula(lRowResult)         Next lRowResult         wksResult.Range("A1:C" & UBound(arrResult, 1)).Value = arrResult     End If     Application.ScreenUpdating = True End Sub

    Comments are very welcome.

    Thank you.

    Gaetan

20. Pingback: Rechercher les liaisons externes dans un classeur lié à une validation - XLérateur

21. I’d prefer

    Visual Basic

    Sub M_snb() On Error Resume Next Set sn = Sheet1.Cells.SpecialCells(-4123) If Err.Number = 0 Then ReDim sp(sn.Count, 2) For Each it In sn If InStr(it.Formula, "\[") Then sp(y, 0) = it.Parent.Name sp(y, 1) = it.Address sp(y, 2) = "'" & Mid(it.Formula, 2) y = y + 1 End If Next Sheets.Add(, Sheets(Sheets.Count)).Cells(1).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp End If End Sub

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

    Sub M_snb()     On Error Resume Next     Set sn = Sheet1.Cells.SpecialCells(-4123)          If Err.Number = 0 Then         ReDim sp(sn.Count, 2)           For Each it In sn           If InStr(it.Formula, "\[") Then             sp(y, 0) = it.Parent.Name             sp(y, 1) = it.Address             sp(y, 2) = "'" & Mid(it.Formula, 2)             y = y + 1           End If         Next                 Sheets.Add(, Sheets(Sheets.Count)).Cells(1).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp     End If End Sub

Posting code? Use <pre> tags for VBA and <code> tags for inline.