hyperlink to hidden excel pages within a workbook

[djeffery]

I have a workbook that has multiple sheets in it. On my main page I have buttons that when clicked follow a hyperlink to another page. I would like to be able to hide all of the pages but still hyperlink to those pages. I have tried multiple versions of code to accomplish this, but none of them will work. Any help would be greatly appreciated.





Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

On Error GoTo FixThings

Dim strAddress As String

strAddress = Application.WorksheetFunction.Substitute(Target.SubAddress, "'", vbNullString)

ThisWorkbook.Worksheets(VBA.Left$(strAddress, VBA.InStr(1, strAddress, "!") - 1)).Visible = xlSheetVisible

Application.EnableEvents = False

Target.Follow

FixThings:

Application.EnableEvents = True

End Sub









Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

'Updateby Extendoffice

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_FollowHyperlink(ByVal Target As Hyperlink)

Dim shtName As String

'shtName = Target.Name

shtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)

Sheets(shtName).Visible = xlSheetVisible

Sheets(shtName).Select



End Sub







Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

LinkTo = Target.SubAddress

WhereBang = InStr(1, LinkTo, "!")

If WhereBang > 0 Then

MySheet = Left(LinkTo, WhereBang - 1)

Worksheets(MySheet).Visible = True

Worksheets(MySheet).Select

MyAddr = Mid(LinkTo, WhereBang + 1)

Worksheets(MySheet).Range(MyAddr).Select

End If

End Sub

 

[Misca]

Welcome to the board!

I think there might be a lot easier (?) solution to your problem: What if - instead of hiding the worksheets - you'd just hide the worksheet tabs? The sheets would still be there but you couldn't see / access them without using the hyperlinks.

To hide the tabs select the Advanced from the Excel Options (File -> Excel Options -> Advanced) and scroll down to the Display options for this workbook section. Uncheck the Show sheet tabs selection and you're done.

Just remember to create your hyperlinks before hiding the tabs.

 

[djeffery]

Very nice. Simple is always better. Works great. Seems to fit my need.

Thanks for you help.