Hyperlink mouseover formula help!

[P0lar]

Hi,


I've used the hyperlink formula trick from the (very nice) dashboards examples elsewhere on the site to run a user defined function when I put the mouse pointer over a cell =IFERROR(HYPERLINK(MyMouseOverEvent(),""),"Text").


I also have a macro which runs when I select the cell with the hyperlink but I now want to change the display text in the hyperlink formula based on this. I'd normally just change the "Text" bit of the hyperlink formula to e.g. A1, then get the macro to change the value in cell A1 to change the display text, but excel thinks that I'm then using a real hyperlink and complains that it can't find the source file when I click on the cell...


Is there a way other than by using something like find / replace in my macro to dynamically adjust the display text in the hyperlink???

 

[cacos]

Not sure exactly what your macro is doing, but if you are using Chandoo's hyperlink UDF the correct sintax would be:


=IFERROR(HYPERLINK(MyMouseOverEvent("")),A1)


And the UDF will print whatever's between the quotation marks to the defined Range on your module:

[pre]

Range("YouRangeName") = seriesname.Value

[/pre]

 

[P0lar]

Thanks for spotting that!


Went back over the hyperlinks, I'd picked up the slight differences from another section of my sheet but not realised I'd changed them from the original. Some slight tweaks to the selection change code later and it's working.

 

[P0lar]

... or so I thought! the changes I made have done something odd to the macro and some of the lines in the code don't work anymore (others are fine).


My mouseover formula is:

If Not Application.Intersect(Application.Caller, Range("i5055")) Is Nothing Then

Range("d67") = Application.Caller.Row

Range("e67") = Application.Caller.Column


I'm then using D & E67 to find cells adjacent to the triggered hyperlink and conditional formats to highlight them. This bit is working okay without the next stage!


When I click on the cell, I have an selection change macro running which I'd like to update the "Text" part of the hyperlink original post

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("I50:o55")) Is Nothing Then
SendKeys "{ESC}" ' this is to cancel the hyperlink "can't find source location" alert
L_Val = Chr(34) & Range("G61") & Chr(34) 'G61 contains the new text I want
i = Target.Row ' this always works
j = Target.Column ' this wierdly stops working in some cases???
Cells(i, j).Value = "=IfError(Hyperlink(MyMouseOverEvent())," & L_Val & ")"
End If

[/pre]

I've also tried adding =choose(A1,iferror(hyperlink...),"Text1","Text2",etc.) then changing A1 in the selection change macro to update the cells (I don't need the hyperlink once the name is updated) but the hyperlink still fires even if A1 <> 1

 

[Luke M]

More details...

The exact problem seems to be that if you click on the cell, XL tries to follow the hyperlink. If you use the arrow keys to select the cell, everything is okay. I've looked at using the Follow_hyperlink event to interrupt the change, but it doesn't seem to get called in time.


Afraid I don't have anything to contribute towards a solution, but maybe this info will help someone. =/

 

[P0lar]

Thanks,


I had the same problem using the follow hyperlink approach but I think I may have a new route, if I put a rectangle over my table and set the fill to white, 100% transparent, then the mouseover hyperlink seems to get picked up "through" the transparent shape but I can only click on the shape, not the cell. As I'm using the mouseover to find what cell I'm in anyway, then I should be able to use a normal macro assigned to the shape to update the cell values without a selection change (hopefully...!)

 

[Luke M]

Nice trick! Thanks for sharing.

 

[P0lar]

Quick update - linking a macro to a shape rather than to the selection change event worked but for some reason the code was only able to change a single cell, further cell changes on subsequent lines of the macro were ignored but I adjusted the sheet accordingly and it's now doing what I was after. I have no idea why this happened (if anyone has any suggestions...?)


Thanks for the help!

 

[Jordan]

P0lar - I'm glad to hear that it's working! I'd be very interested in taking a look at your workbook, if you wouldn't mind posting it here.


You can also handle interactive hyperlink "clicks" without the Worksheet_SelectionChange. I've written about that, here: http://optionexplicitvba.blogspot.com/2012/09/handling-rollover-clicks-without-using.html. For my own projects, when I want to change the friendly text of the interactive hyperlink, I normally point the friendly text to some array off to the side. For example, I might write something like:

=IFERROR(HYPERLINK(MyMouseOverEvent(),Index(MyArray, SelectedIndex)),Index(MyArray, SelectedIndex))

Where MyArray is some array off to the side with the required information - and SelectedIndex is the location of the information you want to pull from the array.

 

[P0lar]

Thanks Jordan,


Think I follow your logic, I'll give it a try as it may help to streamline my workbook (with it's astonishing number of hidden things).


Unfortunately I can't upload files from the network I'm on but I'll be putting in some additional bits over the next few weeks as workloads permit and maybe able to get an upload once it's in better shape.