Checking if last click was a Hyperlink

[Reporter]

Hopefully a simple question from a novice VBA user.

I have a Workbook with a few Worksheets. Some simple VBA (below) ensures that when the Workbook is opened, it always opens on the "INDEX" Worksheet, just below the Freeze Panes level. Also when any other Worksheet is opened, the Worksheet will also always open just below the Freeze Panes level.

However, on my "INDEX" page I also have some hyperlinks that could take me to a specific bookmark on a Worksheet. What I need to be able to do is somehow say, "if a hyperlink was selected, ignore the code that takes me to just below the Freeze Panes level".

I expect it is just a line or two of code but can't seem to find the right syntax anywhere.

Please Login or Register  to view this content.

Help please!

[DonkeyOte]

Assuming the hyperlinks are not generated by HYPERLINK worksheet function then try replacing your code in ThisWorkbook with the below:

Please Login or Register  to view this content.

[shg]

Maybe like this:

Please Login or Register  to view this content.

[Reporter]

Thanks for such a quick reply.

Tried your code, but then if I open the Workbook and click on the Hyperlink on the INDEX Worksheet, it doesn't take me to the bookmark on Sheet2.

Re. I am attaching a simple example if that helps.

Thanks again

[DonkeyOte]

Ah, the activate is fired before the follow hyperlink event, I didn't appreciate (read: test) that.

(also, not that it makes any different there's a typo in my prior post - the final line in the Activate event should have been a reset to False not True!).

[DonkeyOte]

I'm afraid I'm struggling to come up with anything watertight given a Hyperlink cell can be active without being followed... for ex. the Sheet Selection Change event fires before the subsequent Sheet Activate event but you could have a Hyperlink cell active on the initial sheet but activate the subsequent sheet without having followed the Hyperlink.

Hopefully someone else has something ... ?

[DonkeyOte]

Perhaps (for a change) I'm being dense... does the below (ThisWorkbook) work for you - replacing the old code in full

Please Login or Register  to view this content.

So code is per your original post but with the added FollowHyperlink event.

Might not be 100% successful mind.

[Reporter]

Hi DonkeyOte,

Many thanks for taking the time to look at this.

Yes, it looks like the code in your last post works. It's the same as my original code, but with the extra 'Private Sub' for the hyperlink added, isn't it?

Please could you just giving me a very brief description as to what the lines in the extra bit do, just so I can understand fullly.

Thanks again

[DonkeyOte]

Essentially the SheetActivate event will fire before the FollowHyperlink event - ie in terms of order:

-- Hyperlink, resident on SheetX, linked to SheetY is clicked
-- Excel Navigates to SheetY location, in so doing activating the Sheet Activate Event (SheetY)
-- Sheet Activate Event forces cell selection to be that below Split
-- Hyperlink Event invoked
-- Hyperlink Event disables Events then (in essence) navigates to hyperlink cell location before reinstating Events

In reality if you don't have a SelectionChange event active there's no need to toggle Events (given the selection of the Range will not invoke the Activate event)