Hyperlink Keyboard Shortcut Update
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.
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.
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.
This links to a file named CurrentFileName_Backup.pdf. The first thing the code does is remove the last parenthesis.
Next it removes the function name. It doesn’t remove the equal sign because I’ll need that for the Evaluate function later.
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.
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 , 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
The Evaluate function turns into the result of the formula (the file is named JE35.xlsm).
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.