locked
Hyperlink to HIDDEN sheets? RRS feed

  • Question

  • Hi All......

    Ok, now with Hanz's help, I have a macro that will make a list of all the sheets in the workbook and create a Hyperlink to them (see below).....it works great! 

    Sub ListSheets2()
    'HansVogelaar 122512
        Dim wsh As Worksheet
        Dim i As Long
        Dim strName As String
        On Error Resume Next
        Set wsh = Worksheets("MainMenu")
        On Error GoTo 0
        If wsh Is Nothing Then
            Set wsh = Worksheets.Add(Before:=Worksheets(1))
            wsh.Name = "MainMenu"
        Else
            wsh.Range("A:A").Delete
        End If
        For i = 2 To Worksheets.Count
            strName = Worksheets(i).Name
            wsh.Hyperlinks.Add Anchor:=wsh.Cells(i - 1, 1), _
                Address:="", _
                SubAddress:="'" & strName & "'!A1", _
                TextToDisplay:=strName
     '           Sheets(strName).Visible = False 'hides each sheet during process
               
        Next i
        Columns("a:A").AutoFit
       
    End Sub

    I've added a line to hide each sheet during the process, thinking the Hyperlinks would still call them up........no joy now.  The hyperlinks don't work to the hidden sheets. 

    Anybody know how to make that work?, I hope, I hope.

    Any help would be much appreciated,

    Vaya con Dios,

    Chuck, CABGx3


    Chuck, CABGx3

    Wednesday, December 26, 2012 2:50 AM

Answers

  • In the ThisWorkbook module paste the below code.

    Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, _
        ByVal Target As Hyperlink)
    
        Dim s As String
        
        If Sh.Name = "MainMenu" Then
            Application.EnableEvents = False
            
            s = Left(Target.SubAddress, InStr(Target.SubAddress, "!") - 1)
            s = Replace(s, "'", "")
            
            Worksheets(s).Visible = True
            Target.Follow
            Application.EnableEvents = True
        End If
    End Sub
    

    This will trap hyp and check the hyperlink's target,then get the name of sheet, then imitate same action by Target.Follow


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Marked as answer by CABGx3 Wednesday, December 26, 2012 7:23 PM
    Wednesday, December 26, 2012 9:59 AM
    Answerer
  • Since we only need to trap the FollowHyperlink event in the MainMenu sheet, there is no need to use the Workbook_SheetFollowHyperlink event in the ThisWorkbook module. It's easier to use the FollowHyperlink event of the MainMenu sheet itself. You have to create this after the first time you run the ListSheets macro.

    - Right-click the sheet tab of MainMenu.

    - Select View Code from the context menu.

    - Copy the following code into the worksheet module:

    Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
        Dim s As String
        Application.EnableEvents = False
        s = Split(Target.SubAddress, "'")(1)
        Worksheets(s).Visible = True
        Target.Follow
        Application.EnableEvents = True
    End Sub

    I used the Split function to extract the sheet name from the SubAddress.

    Regards, Hans Vogelaar

    • Marked as answer by CABGx3 Wednesday, December 26, 2012 7:24 PM
    Wednesday, December 26, 2012 12:05 PM

All replies

  • In the ThisWorkbook module paste the below code.

    Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, _
        ByVal Target As Hyperlink)
    
        Dim s As String
        
        If Sh.Name = "MainMenu" Then
            Application.EnableEvents = False
            
            s = Left(Target.SubAddress, InStr(Target.SubAddress, "!") - 1)
            s = Replace(s, "'", "")
            
            Worksheets(s).Visible = True
            Target.Follow
            Application.EnableEvents = True
        End If
    End Sub
    

    This will trap hyp and check the hyperlink's target,then get the name of sheet, then imitate same action by Target.Follow


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Marked as answer by CABGx3 Wednesday, December 26, 2012 7:23 PM
    Wednesday, December 26, 2012 9:59 AM
    Answerer
  • Since we only need to trap the FollowHyperlink event in the MainMenu sheet, there is no need to use the Workbook_SheetFollowHyperlink event in the ThisWorkbook module. It's easier to use the FollowHyperlink event of the MainMenu sheet itself. You have to create this after the first time you run the ListSheets macro.

    - Right-click the sheet tab of MainMenu.

    - Select View Code from the context menu.

    - Copy the following code into the worksheet module:

    Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
        Dim s As String
        Application.EnableEvents = False
        s = Split(Target.SubAddress, "'")(1)
        Worksheets(s).Visible = True
        Target.Follow
        Application.EnableEvents = True
    End Sub

    I used the Split function to extract the sheet name from the SubAddress.

    Regards, Hans Vogelaar

    • Marked as answer by CABGx3 Wednesday, December 26, 2012 7:24 PM
    Wednesday, December 26, 2012 12:05 PM
  • There is a if condition which checks for existence of MainMenu.If the sheet does not exist then it is added.

    So a chance is there that MainMenu is not existing in workbook.That's why i used Workbook event.

    As ever,your code is always shorter,smarter!!!!


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Wednesday, December 26, 2012 2:38 PM
    Answerer
  • So a chance is there that MainMenu is not existing in workbook.That's why i used Workbook event.

    That's a valid point. The code that I posted requires that the MainMenu sheet already exists.

    Regards, Hans Vogelaar

    Wednesday, December 26, 2012 2:42 PM
  • Thank you most kindly, your code solves my problem perfectly!

    Many thanks,

    Vaya con Dios,

    Chuck, CABGx3


    Chuck, CABGx3

    Wednesday, December 26, 2012 7:36 PM
  • Thanks Hanz, your code works perfectly as well.

    Thanks again,

    Vaya con Dios,

    Chuck, CABGx3


    Chuck, CABGx3

    Wednesday, December 26, 2012 7:37 PM