private sub worksheet_followhyperlink ...

[mbt2103]

My code:

Private Sub worksheet_followhyperlink(ByVal HL As Hyperlink)
Dim linkReq As Object
Dim linkStatus As Integer
Dim sourceCell As Range
Debug.Print HL.Address
Application.EnableEvents = False


linkStatus = 200
If Dir(HL.Address) = "" Then linkStatus = 404


If linkStatus = 404 Then HL.Parent.Offset(0, 21).Value = "N"
If linkStatus = 200 Then HL.Parent.Offset(0, 21).Value = "Y"


On Error Resume Next
If HL.Parent = "" Then
    HL.Parent.Hyperlinks.Delete
    GoTo exitSub
End If




exitSub:
Application.ScreenUpdating = True
Application.EnableEvents = True


End Sub

Works if the file is present, but doesn't even trigger if the file is absent.
Would love to capture the "Cannot open the specified file" alert and code it as data on my sheet, but I'm not sure how. I would also like to attempt to automatically correct broken links. Any thoughts on how to get this thing to fire?