Trying to replicate the "Rollover Hyperlink trick"

[Jan Pingel]

Hello

I was trying to replicate the

 =IFERROR(HYPERLINK(VBAFunction(Parm)),"cell string")

- but for a little different purpose:

I was trying to have a hover over automatically "unhide a section" or "hide a section'.
Example attached.

For some reason, I don't get any of the hyperlinks to even execute. The hyperlinks are located in the "Template" sheet on cell A4 and D4 - with two different approaches.
I placed an actual button on the "Setup" to show what I actually tried to accomplish - it is using the exact same function.

Did I miss something critical, or is what I am trying to do just to complex for the Hyperlink function?

Thanks for you help

an

 

[Luke M]

I think you've run into the problem that Functions can only do so much, and aren't allowed to hide/unhide rows. I even tried to be "sneaky", and have the hyperlink-UDF make a change in a cell, and then have a Change_event macro written to try to hide the row, but the change_event never gets called. Looks like you'll have to actually click a button to make it work.

 

[Somendra Misra]

@Luke M

Even two days back I was trying to trigger the WorkSheet_Change event macro where cell was getting the value from a linked dropdown, but the event was not taking place when I clicked on the cell and pressed F2 and enter than only event triggered. Is this normal ?

Is change event triggered only by manual change or it can be triggered by calculation change also?

Regards,

 

[Luke M]

@Somendra Misra
In strict sense, only detects a change in cells' formula(s). So, that can be a hard constant (as in VB, you can write
Range("A2").Formula = 5
and still be good), or as your test found, if you 'edit' the cell's formula. If a formula's result changes, it won't get triggered. Have to use the broader Sheet_Calculate event if need to go that way.

 

[Somendra Misra]

gr8...Thanks @Luke M

Regards,