To detect the pesky links to external validation that trigger errors when opening the file

VBA Excel find external validation

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