Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Hyperlinks

0

Hello

I need macro code to create hyperlink to a hidden sheet of the same excel workbook?

Answer
Discuss

Answers

0
Selected Answer

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.

Discuss
0

Have a look at the macro code in the tutorial:

Visible_Text

Modify/adapt this to suit your needs to insert a hyperlink where desired.

Cheers   ;-}

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login