+ Reply to Thread
Results 1 to 4 of 4

hyperlink to a hidden sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    02-02-2010
    Location
    playa del carmen, mexico
    MS-Off Ver
    ms office 2007
    Posts
    52

    hyperlink to a hidden sheet

    I have a workbook that has many hidden sheets within it (for many reasons). I am looking to create a worksheet with hyperlinks to these sheets, so that I can access them at any point a little easier than I do now.
    So far, I have written the code to scan through each worksheet in the workbook and add those with
    .visible=false
    to the page, and add a hyperlink to that sheet from the name listed.
    These hyperlinks work if the sheet is unhidden, but obviously this defeats the purpose of the entire task.
    I have tried playing around with a few ideas in a
    worksheet_followhyperlink
    event but cannot come up with a solution.
    I am sure it is pretty simple, but would appreciate a little help in finding the right solution.

    Thanks
    Last edited by globalpontoon; 05-10-2011 at 01:12 PM.

  2. #2
    Registered User
    Join Date
    02-02-2010
    Location
    playa del carmen, mexico
    MS-Off Ver
    ms office 2007
    Posts
    52

    Re: hyperlink to a hidden sheet

    OK, I've got one stage further.

    My code is as follows

    Option Explicit
    Private Sub Worksheet_Activate()
    
    Me.Visible = xlSheetVisible
    
    Me.Cells.ClearContents
    Cells(1, 1) = "Hidden Sheets"
    
    Dim lastrow As Integer
        lastrow = 2
    Dim sh As Worksheet
    
    For Each sh In Worksheets
        If sh.Visible = False Then
            Cells(lastrow, 1) = sh.Name
            Me.Hyperlinks.Add anchor:=Cells(lastrow, 1), Address:="", SubAddress:=sh.Name
            lastrow = lastrow + 1
        End If
    Next sh
    
    Me.Columns(1).EntireColumn.AutoFit
    
    End Sub
    Private Sub Worksheet_Deactivate()
    
    Me.Visible = xlSheetHidden
    
    End Sub
    Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    
    Sheets(Target.SubAddress).Activate
    
    End Sub
    At this moment, it will come up with an error, due to the subaddress value of hyperlink.add not having an actual cell range, but once I click on the message box, it opens the hidden sheet.
    If I put a cell range ("A1" for example - and obviously with the additional quotation marks etc) onto the subaddress it no longer activates the sheet using the followhyperlink event.

  3. #3
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: hyperlink to a hidden sheet

    globalpontoon,

    See attached workbook. Code is in the Master worksheet. Is something like that what you're looking for?

    Hope this helps,
    ~tigeravatar
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-02-2010
    Location
    playa del carmen, mexico
    MS-Off Ver
    ms office 2007
    Posts
    52

    Re: hyperlink to a hidden sheet

    That should do the trick nicely.

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1