Detect whether hyperlink formula was clicked/followed

Is there a way to detect whether a hyperlink formula has been clicked/followed - i.e. the user arrived on the sheet/cell following the hyperlink rather than their own navigation. If they followed the hyperlink, I'd like to run some code. The hyperlink is a volatile one (the result of a Match/Index or Vlookup) rather than something that could be pre-defined.

Because I've got quite a few hyperlinks in the spreadsheet, I've been using the hyperlink formula rather than manual hyperlinks. I'm aware of some problems that Excel treats these formula hyperlinks differently...

If it's not possible, and I simply remove the hyperlink formula and show the result of the Match/Index (i.e. the cell value), how would get a macro to go to that result? (e.g. if they double click on the cell they'll be taken there - I'm relatively new to VBA and have been exploring the double click function elsewhere in the spreadsheet).

Thanks in advance for any help/guidance.

Thanks for the code Andy.

That works well for most of the cells, but I have a few which are merged - is there a way for it to work with these too? (I don't usually user merged cells, but have inherited a spreadsheet with several).
When I click a hyperlink formula I get a "Run time error 13 type mismatch" error and the debugger highlights the "If InStr(1, Target.Formula, "HYPERLINK(", vbTextCompare) > 0 Then" section of the code.
If I click End and click the hyperlink again, I am taken to the destination cell (as expected) but without your message.
If it makes any difference, the merged cells are merged horizontally (e.g. A1-C1 rather than A1-A3 or A1-C3).

Also, regarding individual/non-merged cells, if I go back and click on the same hyperlink, I'm taken to the destination correctly but the message doesn't appear. If I click a different cell, it works OK, then if I click the original it works OK. Is there a way to re-set it? I'm not sure how likely it is the user will actually do this though.

Cheers,
Graham

Andy,

A related query, I believe...

I have other cells on the sheet which I use a double click event to navigate with. If the user mistakenly double clicks on a hyperlink cell, they are taken to the destination but the cell then goes into edit mode. Is there a way to cancel this?

Thanks,
Graham

Sorry for the dely in replying, Andy - that works fine!

For completeness (and in case anyone else looks at this thread), I've included the completed code I've used (with an additional line to cancel the "edit" if the hyperlkink had been double clicked - is this what you meant, or did you mean to edit the ? (sorry, I'm new to VB)).

Please Login or Register  to view this content.

Thanks Andy. When it seemed to work, I was misled due to some other code assigned to specific cells which I'd used as a workaround. As the code has been pieced together with bits and pieces (and not as tidy as a seasoned pro would have it), I need to dig a little deeper to find where exactly to insert the line.

Regarding your earlier code, that works fine if the user clicks once on the hyperlink (as normal), but if I move onto the cell with the hyperlink using the cursor arrows, it initiates a loop with another macro which lasts a couple of minutes (and resembles a crash). The macro itself (to highlight the destination cell on a large sheet) only takes a second to run under normal conditions. Do you know of a way to allow the code to work if the mouse was clicked on the cell, but not if the cursor keys were used?

Thanks,
Graham

For this particular spreadsheet, I was asked to make it as easy as possible for the user (a common request?)... When I started on it, some work had already been done involving manual hyperlinks (at least I think some had already been set up). I then changed these to the hyperlink formulae as there were quite a few and the results/destination cells were volatile results of formulae.

I got curious about being able to do stuff upon following a hyperlink (e.g. highlight/indicate the cell on a large sheet) and felt at that time I could only do it with a macro - I then started investigation VB (my level is generally record & modify results, rather than being able to program) and found double click events which seemed to work.

I now feel a little stuck as I've got some double click events (go to the cell in a direct link, or go to a cell I've predefined in the macro)and some hyperlink formulae, but now face this problem with the cursor navigating onto a hyperlink cell (but not actually clicking the hyperlink).

What do you mean by adding a shape? Would this simply be an auto shape with a macro assigned? Not sure what you mean about using the shape's location either? (again, VB novice, so please forgive me). Can you elaborate a little?

Thanks,
Graham