Hello
I need macro code to create hyperlink to a hidden sheet of the same excel workbook?
Hello
I need macro code to create hyperlink to a hidden sheet of the same excel workbook?
I think you have us confused as to what you want, create a hyperlink or follow it. In the creation process it's immaterial whether the target sheet is hidden or not. This isn't the case when you want to follow an existing link. Therefore I guessed that you already have the link and want to follow it. The code below will read the targeted tab's name from the hyperlink itself, undhide the sheet if it's hidden, and then proceed to the target range specified in the link.
Sub ViewHiddenSheet()
Const Target As String = "A1" ' change to suit
Dim TabName As Variant
With Range(Target).Hyperlinks
If .Count Then ' skip if no hyperlink at Target
TabName = Split(.Item(1).SubAddress, "!")
If UBound(TabName) Then ' skip if no tab is addressed
Worksheets(TabName(0)).Visible = xlSheetVisible
.Item(1).Follow
End If
End If
End With
End Sub
Make sure you specify the address of your hyperlink in the first line of the code ("Target"). After you got done enjoying the view of the hidden sheet you may wish to hide it again. The code below will do that automatically.
Private Sub Worksheet_Deactivate()
ActiveSheet.Visible = xlSheetVeryHidden ' or xlSheetHidden
End Sub
Install this procedure in the code module of the worksheet targeted by your hyperlink. (It won't work if installed elsewhere.) Observe that there ar two kinds of invisible, namely "Hidden" and "Very Hidden" and if you hide a sheet using code you get to choose. "Very Hidden" sheets aren't listed as "Hidden". Therefore users can't unhide them from Excel's interface. Code is required (or access to the VB Editor) to unhide a VeryHidden tab.
Have a look at the macro code in the tutorial:
Modify/adapt this to suit your needs to insert a hyperlink where desired.
Cheers ;-}