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:
1 |
If Target.Range.Address = Me.Range("D3").Address Then |
I tend to use the caption, which, unlike the example above, is generally meaningful and unique. But not always.
1 |
If Target.TextToDisplay = "Click Me" Then |
If each link points to a different place, I could use the SubAddress
property
1 |
If Target.SubAddress = Me.Name & "!D3" Then |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private msLastAddress As String Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Me.ListObjects(1).ListColumns(1).DataBodyRange) Is Nothing And _ Target.Offset(0, 2).Address = msLastAddress Then Me.Parent.Worksheets(CStr(Target.Value)).Activate End If msLastAddress = Target.Address End Sub |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
Public Sub FollowLink() Dim vaSplit As Variant Dim sForm As String Const sLINKFORM As String = "=HYPERLINK(" Const sIFFORM As String = "=IF(TRUE," On Error GoTo ErrHandler If ActiveCell.Hyperlinks.Count > 0 Then ActiveCell.Hyperlinks(1).Follow Else If InStr(1, ActiveCell.Formula, sLINKFORM) = 1 Then On Error Resume Next ActiveWorkbook.FollowHyperlink Evaluate(Replace(ActiveCell.Formula, sLINKFORM, sIFFORM)) On Error GoTo ErrHandler If Err.Number > 0 Then Err.Clear FollowSplitLink End If Else PTDrillDown End If End If ErrExit: On Error Resume Next Exit Sub ErrHandler: MsgBox Err.Description Resume ErrExit End Sub |
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
1 2 3 4 5 6 7 8 |
On Error Resume Next vEval = Evaluate(Replace(ActiveCell.Formula, sLINKFORM, sIFFORM)) If TypeName(vEval) = "Variant()" Then ActiveWorkbook.FollowHyperlink vEval(1) Else ActiveWorkbook.FollowHyperlink vEval End If On Error GoTo ErrHandler |
Now if the result is a Variant array, it pulls the first element.
“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.
Beautiful! Thanks.
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/.
@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]
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?
@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”)
Guess my last comment was a bit hasty. My RunMacro() function stopped working. Not sure why.
=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.
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.
@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())”)
@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.
Yeah, well you can’t make structural changes to the workbook like adding new sheets or workbooks using this method.
But you can still do quite a lot of other useful stuff like updating ranges, defining names, or creating / modifying worksheet objects (hyperlinks, charts or emails).
I have found this sufficient for creating simple dashboard controls. (See eg: http://stackoverflow.com/questions/32660492/running-vba-from-a-hyperlink/32665442#32665442).
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
Just need to replace: ‘0 Then’ with
‘<> 0 Then’ in above code
@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
@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.
My earlier comment (July 3) described the versatile RUN::COMMAND technique. Then Lori gave me an idea for a simpler way to launch a command from a hyperlink. Here are two examples:
=HYPERLINK("#RunCommand(""Notepad.exe MyNote.txt"")")
=HYPERLINK("#RunCommand(""cmd.exe /k dir"")")
Here is an outline of the RunCommand function:
Instead of the HYPERLINK function, SuperLink or Ctrl+K can be used to create a Hyperlink object. In this case, Hyperlink.Address becomes blank and .SubAddress gets the target function RunCommand(…). But this .SubAddress cannot be viewed using Ctrl+K, so I built the following Ctrl+Shift+K macro:
My earlier comment (July 3) also described the versatile SHEET::NAME technique. But now there is a simpler way for a hyperlink to activate a sheet without specifying a cell (including a chart sheet). For example:
=HYPERLINK("#SheetName(""Sheet1"")")
Here is an outline of the SheetName function:
This requires use of Lori’s delayed macro method, which I have formalized here: https://drive.google.com/file/d/1k3IUFE1HTapH1GIdEpR4iXl6G6AvPatu/view?usp=sharing
In my last comment, I forgot this Post method does not reproduce VBA’s not-equal operator. I should have used something like
If Not Err.Number = 0 Then …
in two places above.
You can use <pre> tags to format VBA or <code> tags for monospace and no formatting or for inline.
@Dick: Thanks for updating my earlier posting, but the first part of the SheetName function got lost. Here are both parts again (if I use the tags correctly):
Oops. Sorry about that. I messed that up when I edited. Thanks for posting the correction.
@J. Woolley, glad this method was useful and good idea to add a shortcut for editting hyperlinks. It’s a little inconsistent that hyperlink text shows in the screentip but not in the dialog.
It seems Run / OnTime commands allow one to pass comma separated VBA arguments as text, so in the previous example one can enter as subaddress:
#RunMacro("MyMacro [A1]")
And to call VBA properties from the sheet, one can then use:
=RunVBA("[A1].Hyperlinks(1).Subaddress")
=RunVBA("[A1].Hyperlinks(1).ScreenTip")
where the following code is copied into a new module:
@Lori, I’ll bet that’s cool, but it’s a little hard to understand. I’ll work at it. (Brief explanation would be appreciated.)
The Object model says Run can take arguments but the procedure run by OnTime cannot take arguments. I tried using Run instead of OnTime in your earlier RunMacro and it worked OK except when the macro had an error; in that case, Excel crashed. Excel didn’t crash if the macro was run by OnTime, so I decided to restrict RunMacro to macros (i.e., Public or Private Sub without arguments). OnTime doesn’t seem to care whether the Sub is Public or Private, and it doesn’t appear in the user’s list of macros if it’s Private.
@J. Woolley, On my setup arguments can be passed as text to both Run and OnTime but they need enclosing in single quotes (”) – i should really test more thoroughly before posting as i forgot the extra quotes again in the example above(!)
So this works with the code sample included in your link:
=HYPERLINK("#runmacro(""'MyMacro 1,2,3'"")")
=HYPERLINK("#runmacro(""'MyMacro cells(1,1),cells(1,2)'"")")
Where MyMacro is declared to take a variable number of arguments:
Basically the idea of the RunVBA UDF is to store the result of the first argument passed to the run function as a module variable (like a property) and then set this as the return value of the function. SetV should have been a Sub as there is no return value, I added Option Private Module to hide functions from the user interface.
Maybe there’s a better way? You could also declare the return variable V within the RunVBA function and pass this as another argument to SetV in order to set it, but i couldn’t think of a neat way to implement that idea.
@Lori, I understand your two examples:
=HYPERLINK("#RunMacro("" 'MyMacro 1,2,3' "")")
=HYPERLINK("#RunMacro("" 'MyMacro Cells(1,1), Cells(1,2)' "")")
But I can’t figure out how to pass a string argument to MyMacro; this doesn’t seem to work:
=HYPERLINK("#RunMacro("" 'MyMacro ""MyString"" ' "")")
Also, this works:
=HYPERLINK("#RunMacro("" 'MyMacro Cells(1,1)' "")")
But these do not work:
=HYPERLINK("#RunMacro("" 'MyMacro [A1]' "")")
=HYPERLINK("#RunMacro("" 'MyMacro Range(""A1"")' "")")
RunVBA is very cool. Try this:
=RunVBA("MsgBox(""Hello World"",vbInformation)")
Additional embedded quotes might fix the issue with passing strings, try with an extra pair of double quotes so there are four lots either side of the text. The hyperlink screentip may be helpful for validating syntax.
I was getting lost in the quotes as well and then stumbled upon vba expressions as possible alternatives. I didn’t see this aspect of the Run function documented – it looks like this syntax ties in with the old RUN macro function which only passes a single string and is presumably called under the hood. Square brackets did work for me after resolving above issues though there are caveats with that notation used in conjunction with udfs.
Along with Kevin C I would have thought that a Defined Name would have done the trick. You can also create a dynamic defined name that gets around someone deleting a row/column that included the name. I wrote a blog post on creating an unbreakable Hyperlink. (you can break it by deleting the whole sheet – but that means the HL is redundant anyway)
https://a4accounting.com.au/excel-unbreakable-sheet-hyperlink-video/
Cool, nice post.
I’ll be writing a little bit about something similar on my site. Http:\\Excelstuff.ca it’s still a long way away but getting there.
I tried to post a comment and got “Wordfence: 403 Forbidden. A potentially unsafe operation has been detected in your request to this site.” My comment is about 50 lines with *pre* tags for VBA and *code* tags for inline, so I prepared it in Notepad and used copy-paste to put it in your COMMENT box. What criteria does Wordfence consider? What criteria does my original comment violate?
Interesting. It claims you were cross-site scripting. I think the offending word you used was “RunVBA” and it thought you were running some script. I whitelisted it so if you want to try it again we can see if it works.
Earlier in this conversation (August 3, 2018) Lori introduced the RunVBA function. I have used recursion to reduce it to a single Excel UDF and renamed it VBAResult:
See examples of use in the code above. The function’s text argument should be a VBA expression permitted on the right side of an equal sign which results in a Variant value on the left side of the equal sign (Result = Expression). Here’s an example that returns 1 (vbOK) after MsgBox is dismissed:
=VBAResult("MsgBox(""Hello World!"")")
But this is incorrect and returns a #VALUE! error:
=VBAResult("MsgBox ""Hello World!""")
An Expression that represents an Object simply returns Expression (the Object), so this example returns ActiveWorkbook:
=VBAResult("ActiveWorkbook")
But this example returns the workbook’s filename:
=VBAResult("ActiveWorkbook.Name")
If Expression represents an array, only its first value is returned (unless you prefer #VALUE! error); this example returns Now:
=VBAResult("Split(""Now is the time"")")
This function might not be very useful, but it is interesting. The magic is done by apostrophes in the Run statement, but I don’t know why. I have tried no apostrophes or different arrangement of apostrophes or added parentheses or Evaluate instead of Run, but the statement in the listing is the only one that works. Except for a filename and exclamation point, it is similar to running a macro in another file like this:
Application.Run "'My File.xlsm'!MyMacro"
Lori thought the magic might be related to “the old RUN macro function which only passes a single string.” I looked at RUN in the Excel 4.0 Macro Functions Reference and did not find anything about apostrophes.
Does anyone have a better explanation for this use of apostrophes with the Application.Run method?