Contacts search results produce error when email address is clicked

TheJay

Active Member
Joined
Nov 12, 2014
Messages
364
Office Version
  1. 2019
Platform
  1. Windows
1653987923553.png

1653988002217.png


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).

1653988259432.png


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

 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Try adding this as the 1st line of code in the event
VBA Code:
If Target.Range.Column <> 1 Then Exit Sub
 
Upvote 0
Solution
Hello Fluff, thanks for your reply. I'm not totally sure where you mean it should go.
 
Upvote 0
Just below the Dim statement.
So, like this?

VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top