Capturing a followhyperlink event in Excel 97

  • In Excel 97, it seems that Workbook_SheetActivate() is not called after clicking on a hyperlink, which takes you to a different worksheet. At present, I carry out some processing when switching between sheets. This all becomes 'out of sync' when the hyperlink problem comes into play.


    It seems there is a Worksheet_FollowHyperlink event you can check for in more recent versions of Excel, but this isn't available for Excel 97.


    Any workarounds?

  • Hi


    In later version of Excel the activating of a Worksheet via a Hyperlink fires the Sheet_Activate Event.


    Perhaps you could have your Hyperlinks go to A65536, then use this


    Code
    1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    2. If Target.Address = "$A$65536" Then Range("A1").Select
    3. End Sub


    Not too sure if this will work in Excel 97 though and I cannot test it.

  • Dave's SelectionChange solution is the way to go for xl97 & Hyperlinks.


    None of the events are fired until the Hyperlink is resolved in xl97 & as stated the WorkSheet_Activate event is not trigured at all.
    If you have many Hyperlinks you may prefer a more generic solution where you dont have to code for every Target Address. I normally hide the column to the left of my Target Cells & use code like this:(need to force a selection change ready for next time).[Code goes in WorkSheet Class Module]

    Code
    1. Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    2. If Target.EntireColumn.Hidden Then
    3. Target.Offset(0, 1).Select
    4. 'run any other code
    5. End If
    6. End Sub


    Another more complex way if you require to know where the Hyperlink was activated & the destination:(this time hide the column to the right of the cell containing the Hyperlink).[code goes in ThisWorkbook Class Module]


    The above code is designed to only run if 1 cell with a Hyperlink to a different sheet in the same workbook is activated. This time it changes the selection of the origin cell ready for next time & gives a msgbox of Origin & Target. Also this will do nothing if a Hyperlink is to another Workbook or Webpage, etc.
    You can then pass the Sh, Target & WS variables to other subs if required.