Contacts search results produce error when email address is clicked

[TheJay]

The workbook functions well, except if someone clicks on an email address on the search results page. It brings up the above error and I either need to stop the error coming up so that it doesn't stop the whole workbook functioning (and actually brings up an email as you would expect when clicking on an email address) requiring all open workbooks to be closed and reopened before all macro functionality resumes or to disable the ability to click on email addresses on the page (whilst still allowing the hyperlinks in the left column to be clicked to take the user to the cell within the workbook that the data is held in and from there, click on the email address).

Could someone please share their thoughts/solution? Many thanks.

Contacts Sample.xlsm

drive.google.com

 

[Fluff]

Try adding this as the 1st line of code in the event

If Target.Range.Column <> 1 Then Exit Sub

 

[TheJay]

Hello Fluff, thanks for your reply. I'm not totally sure where you mean it should go.

 

[Fluff]

Just below the Dim statement.

 

[TheJay]

Just below the Dim statement.

So, like this?

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim wsName As String
    If Target.Range.Column <> 1 Then Exit Sub
    Application.EnableEvents = False
    If InStr(1, Target.Parent, "!") Then
        wsName = Left$(Target.Parent, InStr(Target.Parent, "!") - 1)
        If wsName Like "*'" Then wsName = Left$(wsName, Len(wsName) - 1)
    Else
        wsName = Target.Parent.Name
    End If
    Sheets(wsName).Visible = -1
    Sheets(wsName).Activate
    Application.EnableEvents = True
End Sub

 

[TheJay]

Wow, I don't believe it! Thank you so much. That's been a problem for years... Such a small piece of code made all the difference.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[TheJay]

Ah, I have encountered a problem. When I go onto the "Contents" page, the links don't work any more. I'm sure it used to be a protected page, yet the links worked?

EDIT:

After unlocking the sheet, it seems to work again as expected.

 

[Fluff]

They work for me quite happily, although I disabled a lot of your code as I don't like people messing with the layout of Excel.