CODEWITHSUNDEEP
excelvbahyperlink
Selkie
Selkie

Reputation: 1255

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

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.

Upvotes: 0

Views: 655

Answers (1)

Selkie
Selkie

Reputation: 1255

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.

Upvotes: 0

Related Questions