HOW: Click Formula Hyperlink To Trigger Macro

[Henninger5]

I have a column (Column 6) where every row contains an IF() formula
that results in a HYPERLINK() formula if the previous cells in that
row each contain data. In other words, a hyperlink shows up if the
row is filled out. If the hyperlink shows up, and the user clicks it,
I want a macro to be triggered. I want to trigger a macro that draws
upon the data from the other cells in that row.

My first discovery was the Worksheet_FollowHyperlink event. It seemed
perfect. It triggers when a Hyperlink Object is click and captures
that location of the cell where the hyperlink was clicked. However,
it doesn't trigger with Hyperlink Formulas, just Objects.

I've been filling my hyperlink formula destinations with '#' so that
they don't go anywhere, but can I specify a hyperlink destination that
is a trigger for the macro?

Any help would be appreciated.

 

[Dave Peterson]

Personally, I think I would drop the hyperlink idea.

I'd replace it with either a worksheet event (_beforerightclick or
_beforedoubleclick) or even an object (like a button from the forms toolbar) in
the cell.

Or even a single button at the top of the worksheet (freeze row 1) and then use
the activecell's row to do the work in the macro.