How to Modify Worksheet Hyperlink Index

The attached workbook has 13 worksheets in it.

Worksheet "Index" uses an "OnActivate" code to build hyperlinks to the other sheets.

Is there a way to have worksheets that have a property value of "xlSheetVeryHidden" NOT included in this list?

Can it be modified to create two lists: One for worksheets, one for Charts?

Regards,

Thank you to OzGrid for the start on this.
Hyperlink-Index.xlsm
DougDodgeAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

pls try

Option Explicit

Private Sub Worksheet_Activate()

Dim wSheet As Worksheet
Dim chrt As Chart
Dim l As Long

l = 1

    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX TO WORKSHEETS"
        .Cells(1, 1).Name = "Index"
    End With
    

    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name And wSheet.Visible <> xlSheetVeryHidden Then
            l = l + 1
                With wSheet
                    .Range("A1").Name = "Start_" & wSheet.Index
                    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:="Index", TextToDisplay:="Return to Main Worksheet"
                End With

                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet
l = 1
    With Me
        .Columns(2).ClearContents
        .Cells(1, 2) = "INDEX TO CHARTS"
        .Cells(1, 2).Name = "Index"
    End With
    

    For Each chrt In Charts
        If chrt.Name <> Me.Name  And chrt.Visible <> xlSheetVeryHidden  Then
            l = l + 1
                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 2), Address:="", SubAddress:="Start_" & chrt.Index, TextToDisplay:=chrt.Name
        End If
    Next chrt

End Sub

Open in new window

Regards
0
 
DougDodgeAuthor Commented:
Works perfectly...... Thank you once again for your assistance......
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.