Hyperlinks Cease to Function

[Davidph]

I've always managed to find a solution to my Excel problems on this forum but have found nothing that seems to match the problem I am experiencing with Hyperlinks so just I've registered to raise the problem and see if anyone else has seen it and or has a resolution.

I'll try and describe what has been set up and what I am seeing: -

Worksheet A has Hyperlinks from cells to Worksheet B. The are not set using =HYPERLINK(..... A FollowHyperlink procedure on Sheet A carries the content of the clicked cell to Worksheet B. Note the FollowHyperlink is only activated when a cell within a designated Range for all the Hyperlinks is clicked.

The above works absolutely fine, Sheet B has Hyperlinks to Sheet C and there is another 'FollowHyperlink' procedure on Sheet B to carry content to Sheet C.

All works fine when you reselect Sheet A or Sheet B by Sheet Tab and choose another Hyperlink.

However, in trying to be neat and easier for users to use, I added a 'Go Back' Hyperlink (this is outside the Range of 'working' Hyperlinks on the sheet) on Sheet B that simply jumps back to Sheet A and a similar one of Sheet C that goes back to Sheet B.

As soon as I use one, or other, of the 'Go Back' Hyperlinks all the other hyperlinks no longer initiate the 'FollowHyperlink' procedure when clicked. They still move to the destination sheet but there is no further change in data as would happen if the 'FollowHyperlink' procedure had been actioned.

If I close Excel completely at this point and re-open the file the Hyperlinks work properly, with manual switching of worksheets and then fail as soon as I use the 'Go Back' hyperlinks.

I'd be very grateful if someone has a solution to this problem.

Regards,

Davidph

 

[RoryA]

Immediately after using a GoBack hyperlink, if you open the VBE, go to the Immediate window and type:

?Application.Enableevents

and press Enter, does it return False?

 

[Davidph]

Hi royra, thank you for the quick response.

Yes the suggestion you made below returns FALSE.

Regards,

Davidph

 

[RoryA]

What is the code in your followhyperlink event - there seems to be a flaw in it that is disabling events.

 

[Davidph]

Hi royra, I think I've sussed it thanks to your question.

The FollowHyperlink procedure has an Application.EnableEvents = False as the first call but the Application.EnableEvents = True, to reset, was in the If.....End If call that checks which Hyperlinks were being clicked so wouldn't have been called when the 'Go Back' hyperlink was clicked. EnableEvents was stuck as False. My useless coding!!

Having added a Application.EnableEvents = True directly before the End Sub seems to have sorted it.

Thanks for pointing me in the right direction.

Regards,

David

 

[RoryA]

I suspected as much! As a rule, if you disable events in your code, it's a good idea to ensure that there is only one exit point from your code and that it re-enables events. For example:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
   On Error GoTo err_handle
   Application.EnableEvents = False
   
   ' rest of code here
   ' do NOT use an Exit Sub!
   
clean_up:
   ' reset events
   Application.EnableEvents = True
   ' then exit
   Exit Sub
err_handle:
   ' notify error
   MsgBox Err.Description
   ' make sure to reset events
   Resume clean_up
End Sub