Hyperlink directs to hidden row

Question

I have a workbook that has 2 sheets. Sheet 1 has hyperlinks to several different cells in Sheet 2. The issue is that there are filters in Sheet 2 that will hide rows, so when you try to follow the hyperlink from Sheet 1, the row is hidden and you can't see the target. I'm trying to figure out how to do the following:

• On clicking a hyperlink, determine the target row in Sheet 2
• If target row is hidden, unhide the target row in sheet 2, then follow the hyperlink.

It can stay unhidden after the hyperlink is followed, I'm fine with that. I've struggled with this for the past several days, and have come up with nothing successful. I've tried the "Followhyperlink" function, but I think this is too late - it's already followed the hyperlink, so unhiding the row at that point is too late.

Any suggestions? I'm stumped!

Answer 1

FollowHyperlink is indeed the event handler to use. Put this code in the worksheet module for Sheet1:

Option Explicit

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim hyperlinkParts() As String

If ((Target.Type = msoHyperlinkRange) And (Target.SubAddress <> "")) Then
    If (InStr(Target.SubAddress, "!") > 0) Then
        hyperlinkParts = Split(Target.SubAddress, "!")

        If ((Left$(hyperlinkParts(0), 1) = "'") And (Right$(hyperlinkParts(0), 1) = "'")) Then
            hyperlinkParts(0) = Mid$(hyperlinkParts(0), 2, Len(hyperlinkParts(0)) - 2)
        End If

        Worksheets(hyperlinkParts(0)).Range(hyperlinkParts(1)).EntireRow.Hidden = False
    End If
End If

End Sub

This checks that the hyperlink corresponds to a Range object then splits the target address into the sheet name and the specific cell(s). It then unhides the row(s) which correspond to the target address.

The check for the subaddress being empty is needed for hyperlinks to an external workbook where no particular cell is specified

edit: this approach won't work for hyperlinks to named ranges and I've altered the code to avoid getting an error message with that kind of hyperlink

edit2; code revised to deal with worksheet names containing spaces which were previously causing an error