hyperlink to a hidden sheet

**globalpontoon** · 05-10-2011, 11:47 AM

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

**globalpontoon** · 05-10-2011, 12:00 PM

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.

**tigeravatar** · 05-10-2011, 12:36 PM

globalpontoon,

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

Hope this helps,
~tigeravatar