Skip to content

Instantly share code, notes, and snippets.

@jceresearch
Last active August 22, 2018 19:12
Show Gist options
  • Save jceresearch/048b620fc80b96216f732c909a1ec6c0 to your computer and use it in GitHub Desktop.
Save jceresearch/048b620fc80b96216f732c909a1ec6c0 to your computer and use it in GitHub Desktop.
To detect the pesky links to external validation that trigger errors when opening the file
Public Sub FindExtValidation()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Activate
On Error Resume Next
Set v1 = ws.Cells.SpecialCells(xlCellTypeAllValidation)
If Err.Number > 0 Then
GoTo next_sheet
End If
For i = 1 To ws.UsedRange.Rows.Count
For j = 1 To ws.UsedRange.Columns.Count
If Not Intersect(v1, ws.Cells(i, j)) Is Nothing Then
If InStr(ws.Cells(i, j).Validation.Formula1, ".xl") > 0 Then
ws.Cells(i, j).Activate
MsgBox ("Found")
Exit Sub
End If
End If
Next
Next
next_sheet:
Next
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment