Hyperlink Formula Events

I like to use hyperlinks as user interface elements. They sit nicely in a cell, blend nicely with the surrounding data, and are well understood by users (blue underline means click here). Typically I’ll create a hyperlink that points to itself and then use the FollowHyperlink event to do stuff when it’s clicked.

If a user were to insert rows or columns the hyperlink moves but not the cell it refers to. That is, inserting a column to the left will move the hyperlink to E3, but it will still point to D3. I don’t like it, but it’s not a deal breaker. It means that I can’t use the location of the link to determine which hyperlink was clicked. This is bound to fail:

I tend to use the caption, which, unlike the example above, is generally meaningful and unique. But not always.

If each link points to a different place, I could use the SubAddress property

If there’s a space in the sheet name, there needs to be quotes around it. And if someone changes the sheet name, it mucks up the whole hyperlink. So there are a few things that can go wrong.

My biggest pet peeve is that I can’t use the HYPERLINK() function and the FollowHyperlink event together. That event only responds to inserted hyperlinks, not to links created by a formula. The use case for this is that I could add a column to a Table that had the HYPERLINK() function in it and that formula would automatically expand as the table expanded. Here’s a really contrived example.

When the user clicks a Jump link, it should go to that page. So in the case, the data I need is in the first column (the name of the sheet). Here’s a method I’ve been working on using the SheetChange event. When the user clicks on C2, that cell is selected. Then the link takes the user to A2 and that cell is selected. So I’m looking for a combination of column C then column A.

The module-level variable holds the last cell that was selected. If the currently selected cell is in the first column of the Table and the last cell was two cells to the right, it’s a match. If you’re wondering why I included the seemingly meaningless data in the second column, I’ll tell you. This method fails a lot if the two columns are adjacent because any time you arrow to the left through the table, you’d fire off the code. Having a column B in between fixes that.

You’ve probably already guess that there are a few problems with this. You could arrow to somewhere in the Jumper column and then click in the Number column and trigger the code when you didn’t expect to. Plus whenever you’re dealing with selections, you need to account for multi-cell selections, which I don’t here. But I’m going to try it out and see how it goes.

One side effect of this was that it broke my keyboard hyperlink clicker. It used to look like this.

When you use a ROW() function in the hyperlink address, the Evaluate returns a Variant array and this results in a Type Mismatch error. The relevant parts have been changed to

Now if the result is a Variant array, it pulls the first element.

16 thoughts on “Hyperlink Formula Events

  1. “If a user were to insert rows or columns the hyperlink moves but not the cell it refers to.”

    To make a hyperlink that always points to itself, even after row or column changes, select ‘Link to: Existing File…’, then enter ? (i.e., question-mark) as the hyperlink’s address; this will become blank after first use. Do not select ‘Link to: Place in This Document’, because then you must specify a cell or range.

  2. I agree that hyperlinks make good user interface elements. You might be interested in my SuperLink user-defined function (UDF). SuperLink resolves several issues associated with Excel’s built-in HYPERLINK function, including those you mentioned. The ZIP file with detailed information can be downloaded from Google Drive (click the download arrow in the upper-right corner): https://drive.google.com/file/d/1k05AMbbYKdYPZjyEB7Ys-6utUYzneaSx/view?usp=sharing.

    SuperLink supports the RUN::COMMAND technique, which addresses difficulties when choosing Excel hyperlinks to open command files like EXE, BAT, CMD, VBS, etc. For a description of this technique, see https://drive.google.com/file/d/18YGl1y1XDF1yOE_fDUNoJgVj0qbn15Wk/view?usp=sharing.

    SuperLink also supports the SHEET::NAME technique, which permits an Excel hyperlink to activate any chart sheet or worksheet in a workbook without altering that sheet’s previous selection. For details, see https://drive.google.com/file/d/1mlbFxSr8KmGZy70Zkh2SYd2PYJFF3tog/view?usp=sharing.

    These techniques were first published in May 2018 (with an earlier version of SuperLink) at https://contexturesblog.com/.

  3. @J. Woolley. Nice, wasn’t aware of the ‘?’ trick.

    Other options to avoid ROW() are using structured or r1c1-style references:

    =HYPERLINK(“#tblTabl3[@Number]”,”Jump”)
    =HYPERLINK(“#rc[-2]”,”Jump”)

    Subaddresses are evaluated just like formulas and also allow running some types of macros. For example to update the cell to the right with a timestamp after a click, try entering:

    =HYPERLINK(“#Update(rc[1],now())”,”Update”)

    Where the Update function is included in a code module:

    [CODE]Function Update(R As Range, V) As Range
    R.Value = V
    Set Update = Selection
    End Function[/CODE]

  4. Why not hyperlink to a Defined Name that references the cell with the hyperlink in? Then the Defined Name will ‘move around’ with the hyperlink no matter how many rows or columns are inserted/deleted. Or have I missed something?

  5. @Lori. Nice. I wasn’t aware of the “macro” trick. Your Update() function works because it returns Range, but not if it returns something else like Integer (obviously). It can be a very useful technique.

    On the other hand, if I have a macro like Public Sub MyMacro() and a cell with

    =HYPERLINK(“#MyMacro()”)

    then clicking the hyperlink will run MyMacro twice (because they are always evaluated twice) followed by an Excel message box with “Reference isn’t valid.” One way to avoid this problem is to initiate MyMacro by use of a function similar to your Update() function:

    Function RunMacro(sMacro As String) As Range
    Evaluate sMacro
    Set RunMacro = Selection
    End Function

    Then the following works fine

    =HYPERLINK(“#RunMacro(MyMacro())”)

    Do you have any other suggestions?

    By the way, the following will open MyMacro in the VBE (which you probably knew)

    =HYPERLINK(“#MyMacro”)

  6. Guess my last comment was a bit hasty. My RunMacro() function stopped working. Not sure why.

  7. =HYPERLINK(“#RunMacro(“”MyMacro()””)”)

    Maybe? You could also try ‘Run’ in place of ‘Evaluate’. And I like the hyperlink to VBE – i had stumbled upon it but hadn’t seen this documented anywhere.

  8. The suggestion above assumes MyMacro() is defined as a Function.

    If using “#RunMacro(MyMacro())” where MyMacro() is a Sub, i think the ‘Evaluate’ line can be removed from the RunMacro code and it seemed ok for me.

  9. @J. Woolley, I think you may be onto something here. I was always under the impression that passing a Sub to a Function was not possible – almost by definition. But it seems it is legitimate under very specific circumstances. Another possibility is:

    =HYPERLINK(“#index(rc,MyMacro())”)

  10. @Lori, you are right, of course, about Run vs. Evaluate. But both seem to produce similar results when using HYPERLINK to start a macro. They work fine with a simple macro, but not if the macro actually does something useful. I think this rabbit hole is related to the limitations imposed upon an Excel user-defined function.

  11. The Superlink macro links look cool – i have learned some new tricks from them.

    For anyone that’s interested, here’s some updated code to run any kind of VBA macro from a hyperlink formula (which i nope parses ok!)

    Example formulas:

    =HYPERLINK("#RunMacro(""MyMacro"")")
    =HYPERLINK("#RunMacro(""'MyMacro ""&a1&""'"")")

    VBA code pasted into module:

    Declare PtrSafe Function SetTimer Lib "user32" ( _
    ByVal HWnd As Long, _
    ByVal nIDEvent As Long, _
    ByVal uElapse As Long, _
    ByVal lpTimerFunc As LongLong _
    ) As Long
    'or use lpTimerFunc As Long for 32 Bit Office

    Declare PtrSafe Function KillTimer Lib "user32" ( _
    ByVal HWnd As Long, _
    ByVal nIDEvent As Long _
    ) As Long

    Dim mTimerID As Long
    Dim mMacro As String
    Dim mAppTime As Date

    Private Function RunMacro(sMacro As String) As Range
    mMacro = sMacro
    If mTimerID 0 Then KillTimer 0&, mTimerID
    mTimerID = SetTimer(0&, 0&, 1, AddressOf RunProc)
    Set RunMacro = Selection
    End Function

    Public Sub RunProc()

    KillTimer 0&, mTimerID
    mTimerID = 0
    On Error Resume Next
    If mAppTime 0 Then Application.OnTime mAppTime, mMacro, , False
    mAppTime = Now
    Application.OnTime mAppTime, mMacro

    End Sub

    Sub MyMacro(Optional sString = "Hello!")
    'Set breakpoint in this sub
    MsgBox sString
    End Sub

  12. @Lori, your RunMacro function is very clever. Here are 3 comments:

    1. You have Private Function RunMacro… and Public Sub RunProc(). Did you mean vice versa (Public/Private)?

    2. You have this example usage, which doesn’t work for me (yes, I populated cell A1):
    =HYPERLINK(“#RunMacro(“”‘MyMacro “”&a1&””‘””)”)

    3. You might reference the following to generalize for 32-bit/64-bit:
    http://stackoverflow.com/questions/20269844/api-timers-in-vba-how-to-make-safe/32948677#32948677

  13. @J. Woolley. The method above was taken from another SO post: https://stackoverflow.com/a/8711582 which runs macros from udfs but the same principle applies to hyperlinks (as well as to named formulas in data validation or conditional formats.) And you make great points…

    1. Runproc would be better as Private. RunMacro can be Private as it is called from a Run (OnTime) procedure and is not then visible to the user.

    2. You are right, the second hyperlink formula allows for numbers but not text in the A1 argument. I had tested as an address in a normal hyperlink but then left out some extra quotes needed for the formula equivalent.

    3. And yes, for sure, use a compatibility declaration as shown in that link.

Leave a Reply

Your email address will not be published. Required fields are marked *