Worksheet_FollowHyperlink does not fire if Hyperlink is invalid

[Billy]

Hi,

I have some Cells with Hyperlinks to Word-Documents, but sometimes the
Document doesn't exist. If I click on such a Link I'm getting a message like
"Could not open file..."

Now I want to catch this error using the Worksheet_FollowHyperlink-Event.
But it doesn't fire if the Link is invalid.

Any ideas?

Regards,

billy

 

[Barb Reinhardt]

I created this very basic follow followhyperlink procedure and even if the
links didn't open, they did put something in the immediate window.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim myCell As Excel.Range

Set myCell = Target.Parent
Debug.Print myCell.Address, myCell.Value

End Sub

It sounds like you are opening files with your hyperlinks. You may want to
consider using the 'FileExists' method described here.

http://msdn.microsoft.com/en-us/library/x23stk5t(VS.85).aspx

I prefer something that returns a boolean variable and may have found this
somewhere, but this is what I use.

Function myFileExists(myPath As String) As Boolean
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(myPath) Then
myFileExists = True
'no problem
Else
myFileExists = False
End If

Set FSO = Nothing
End Function

HTH,
Barb Reinhardt

 

[Billy]

Hi Barb,

I created this very basic follow followhyperlink procedure and even if the
links didn't open, they did put something in the immediate window.

hmm, not in my example. Which version of Excel are you using?
My is 2003 - I think this is from Office XP.

It sounds like you are opening files with your hyperlinks. You may want
to consider using the 'FileExists' method described here.

that's what I wanted to do in my exception handler - but as I've written, it
does not fire if eg. the file was renamed after the hyperlink has been
created :-(

Regards,

billy