I have a custom keyboard shortcut, Ctrl+L, to “click” on a hyperlink in Excel. I thought I had posted that code, but I can’t find it. It’s not much.
|
1 2 3 |
If ActiveCell.Hyperlinks.Count > 0 Then ActiveCell.Hyperlinks(1).Follow End If |
It doesn’t work with links created with the HYPERLINK function because a formula doesn’t create a member of the Hyperlinks collection. I fixed it by parsing the formula and trying to follow the link inside.
|
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 |
Public Sub FollowLink() Dim vaSplit As Variant Dim sForm As String Const sLINKFORM As String = "=HYPERLINK(" On Error GoTo ErrHandler If ActiveCell.Hyperlinks.Count > 0 Then ActiveCell.Hyperlinks(1).Follow Else If InStr(1, ActiveCell.Formula, sLINKFORM) = 1 Then sForm = ActiveCell.Formula sForm = Left(sForm, Len(sForm) - 1) 'remove last parent sForm = Replace(sForm, Mid(sLINKFORM, 2, 255), vbNullString) 'Remove function name vaSplit = Split(sForm, ",") If IsError(Evaluate(Join(vaSplit, ","))) Then 'friendly name argument used ReDim Preserve vaSplit(0 To UBound(vaSplit) - 1) End If ActiveWorkbook.FollowHyperlink Evaluate(Join(vaSplit, ",")) End If End If ErrExit: On Error Resume Next Exit Sub ErrHandler: MsgBox Err.Description & vbNewLine & Evaluate(Join(vaSplit, ",")) Resume ErrExit End Sub |
Here’s an example of a HYPERLINK formula I use.
|
1 |
=HYPERLINK(LEFT(SUBSTITUTE(CELL("filename"),"[",""),FIND(".",CELL("filename"))-2)&"_Backup.pdf","Backup") |
This links to a file named CurrentFileName_Backup.pdf. The first thing the code does is remove the last parenthesis.
|
1 |
=HYPERLINK(LEFT(SUBSTITUTE(CELL("filename"),"[",""),FIND(".",CELL("filename"))-2)&"_Backup.pdf","Backup" |
Next it removes the function name. It doesn’t remove the equal sign because I’ll need that for the Evaluate function later.
|
1 |
=LEFT(SUBSTITUTE(CELL("filename"),"[",""),FIND(".",CELL("filename"))-2)&"_Backup.pdf","Backup" |
Next it splits the string on a comma. A comma will separate the link argument from the friendly name argument. This one has more than one comma, but we’ll deal with that later by Joining the array.
|
1 2 3 4 5 6 |
vaSplit(0) = =LEFT(SUBSTITUTE(CELL("filename") vaSplit(1) = "[" vaSplit(2) = "") vaSplit(3) = FIND("." vaSplit(4) = CELL("filename"))-2)&"_Backup.pdf" vaSplit(5) = "Backup" |
The friendly name argument is optional. This example has a friendly name, but not every one will. To determine if the friendly name argument is used, I try to evaluate the string. A legitimate formula with a
|
1 |
, friendly_name |
after it won’t evaluate and will return an error. If that’s the case, I remove the last element of the array and evaluate the remaining elements.
In this case, the Redim Preserve gets rid of element #5, but leaves the others intact. The remaining five elements are joined into
|
1 |
=LEFT(SUBSTITUTE(CELL("filename"),"[",""),FIND(".",CELL("filename"))-2)&"_Backup.pdf" |
The Evaluate function turns into the result of the formula (the file is named JE35.xlsm).
|
1 |
S:\Accounting\General_Ledger_Information\201606\JE35\JE35_Backup.pdf |
Passing that to FollowHyperlink opens the file. Unless it doesn’t exist. Then I get an error “Cannot open the specified file.” and a message box tells me the file name it tried to open. That way I can troubleshoot any problems before I go ask why the backup wasn’t included in this journal entry.
Nice – will add to my list of shortcuts. One possible simplification could be to replace “HYPERLINK(” with “IF(1,” then I’m not sure parsing would be necessary since the friendly name will be ignored if it is present.
And good to see you back, dick..
Genius! Thanks Lori. That code just got a lot simpler.
I`m guessing there is a hard-won reason for the way you specify the ReDim as:
And not like this:
O.k. Now I
ve RTFMd:“…the lower bound of an array is controlled by the Option Base statement. The lower bound is zero if no Option Base statement is present.”
That`s what happens when you spend too much time in VBScript!
Thank you so much for this. I searched the whole internet to find something like this where I can assign a hotkey to it! Awesome!
I am using this script almost daily! So thankful you shared it! One thing I discovered overt time.
If my sheet name is abbreviated using a apostrophe ( ‘ ) the function doesn’t work anymore.
For instance: my sheet name originally looked like “Accounting 2022” and I shortened it to “Accounting’22” then I can’t use the script inside that sheet anymore. I am guessing it’s because of how Excel handles the reference to a sheet?
Any way to get around this?
Thank you very much!
For a formula or a standard hyperlink? If I create a normal hyperlink to a sheet called acc ’22, it looks like this
‘acc ”22′!A1
Excel doubles the internal single quote and both clicking and the macro work.
If I create a hyperlink formula like
=HYPERLINK(“#’acc ”22′!a1″)
then both clicking and the macro work.
If I create a hyperlink formula with only one single quote
=HYPERLINK(“#’acc ’22’!a1”)
then neither works.
The whole problem is that if your sheet name has a space or other special characters, Excel surrounds it in single quotes. And if you have a single quote within, it doubles that single quote.