Macro functions work, still gives "Reference isn't Valid" Error

[jonstr101]

Hello,

I am using the following code to unhide a sheet when accessed through a hyperlink and rehide the sheet when I return to my workbook index:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Application.ScreenUpdating = False
    Dim strLinkSheet As String
    If InStr(Target.Parent, "!") > 0 Then
        strLinkSheet = Left(Target.Parent, InStr(1, Target.Parent, "!") - 1)
    Else
        strLinkSheet = Target.Parent
    End If
    Sheets(strLinkSheet).Visible = True
    Sheets(strLinkSheet).Select
    Application.ScreenUpdating = True
End Sub
 
Private Sub Worksheet_Activate()
    On Error Resume Next
    Sheets(ActiveCell.Value2).Visible = False
End Sub

When I click any hyperlinks in the index, I get a "Reference isn't valid" error, but when I click OK it unhides the sheet and takes me there as it should.

When I go back to the index it hide the sheet as normal.

Any clues from the MrExcel hivemind?

Thank you,
Jon

 

[jasonb75]

Probably because it is not valid until the code runs. You're linking to a hidden sheet so the link is trying to go there before the code runs but can't actually go there until after it runs and unhides the sheet.

The only way around it would be to have the actual hyperlink point to the sheet and cell in which it is contained, then use the code to redirect after the destination sheet has been made visible.
This would most likely mean additional cells to hold the actual destination references as well.

 

[jonstr101]

As it turns out, I'm just stupid. I had changed the names of my sheets but not updated the hyperlinks, so the hyperlink it initially followed was invalid.

After it gave the error for that hyperlink, the code would take over and take me to the requested worksheet.
I switched to a simpler code that appears to work better anyways, and it seems to be more common on this forum.

On the "index" page:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim ShtName As String
        ShtName = Replace(Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1), "'", "")
        Sheets(ShtName).Visible = xlSheetVisible
        Sheets(ShtName).Select
End Sub

On the hidden sheets:

Private Sub Worksheet_Deactivate()
    Me.Visible = xlSheetHidden
End Sub

Thank you.