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
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
Of course Bill Manville’s Findlink would have speeded up the find process quite a bit:
http://www.oaltd.co.uk/mvp
Why not Edit - Find - Find: “]” - Find all ?
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.
Flexfind would have located them without a problem, make sure you check “Objects” and look in formulas.
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.