Clicking on a dynamic hyperlink with VBA to jump to a new position within the same sheet

Question

I have a large chunk of VBA code that generates a result sheet. It's fairly large, and in order to better, faster dig through it, I've added a dynamic hyperlink at the top of the sheet, with a drop-down menu next to it. Drop down the item, click the hyperlink, and you get whooshed over to the part of the spreadsheet you want to get to.

I've been asked to make it even easier, and when you select an item from the drop down menu, to auto-whoosh you to the correct location. So an on-trigger macro to click a dynamic hyperlink.

Ok, so far, so good. Do some googling, and I end up with the following:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("HyperlinkType")) Is Nothing Then ClickHyperlink
End Sub

Private Sub ClickHyperlink()

ThisWorkbook.Names("HyperLinkTotal").RefersToRange.Hyperlinks(1).Follow

End Sub

Unfortunately, this results in a subscript out of range, which apparently can happen with dynamic hyperlinks.

The hyperlink formula for reference:

=IFERROR(HYPERLINK("#Totals!B"&MATCH(HyperlinkType,B:B,0),"Jump to "&HyperlinkType),"Please enter a valid type")

1) How do I fix the subscript out of range issue? 2) Is there a better way than hyperlink(1)? It almost looks to me like it's indexing the hyperlink, and I'm not sure that's exactly what I'm looking for - I'm looking for the hyperlink in the cell, not the first in the workbook. I may be misunderstanding.

Previous instances of this, and similar question on stack overflow: Excel Macro executing Hyperlink shows 'Subscript out of range error' - no answer
Hyperlinks.Follow error: Run-time error '9': Subscript out of range - completely different method used to solve that particular issue (XY problem)
Hyperlink code shows Subscript out of range error vba excel - used a reserved word as a variable

VBA to open Excel hyperlink does not work when hyperlink generated with a formula - Seems to be promising, I think this might solve it.

do the thing that everyone says get rid of to speed up your code. `SELECT` When you use the VBA recorder it give the coding for selecting cells. So if you can can calculate the sheet and cell you want to wind up on, you can do that as a separate chunk of code, and then at the end of it use select to bring you to that cell. Not saying its better, just an alternative approach. — Forward Ed, Feb 04 '19 at 18:48
That's a good point - instead of trying to use the hyperlink object, I could just use the select method instead, which would jump me there as well. Going to look into that, I've managed to trigger a different error which seems promising — Selkie, Feb 04 '19 at 18:50

Selkie · Answer 1 · 2019-02-04T20:40:29.347

Thanks to @Forward Ed, I was able to get it working with select.

Forgive the lazy lack of variables:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("HyperlinkType")) Is Nothing Then ClickHyperlink (Me.Range("HyperlinkType").Value)

End Sub

Private Sub ClickHyperlink(ActuarialString As String)
Dim ResultRow As Long

ResultRow = Me.Range("B:B").Find(ActuarialString).Row

Me.Cells(ResultRow, 2).Select

End Sub

To put it another way: If you want to click on a dynamic hyperlink, you're probably running into the XY problem. Step back, figure out exactly what you're trying to accomplish, and use one of VBA's other tools to do it.

Clicking on a dynamic hyperlink with VBA to jump to a new position within the same sheet

1 Answers1