Turning a table of contents built via a VBA Worksheet_Activate sub-routine into a set of clickable links to the other worksheets within the workbook

[cuddling101]

I am trying to turn a worksheet that is, in effect, a table of contents into a set of links to each of the respective worksheets.

I have the following working Activate_Worksheet event sub-routine -

Private Sub Worksheet_Activate()
    
    Dim wb As Workbook
    Dim ws() As Worksheet
    Dim rng As Range
    Dim rngNow As Range
    Dim wsCount As Integer
    Dim wsColor() As String
    Dim wsPrime As Worksheet

    Dim I As Long
    
    Set wb = ThisWorkbook
    Set wsPrime = wb.Worksheets("Coverage_Statistics") 'Targets Coverage_Statistics for placement of Table of Contents
    
    wsCount = wb.Names("Main_Families_Count").RefersToRange.Value
        
    Set rng = wsPrime.Range("A1:B" & wsCount)
    ReDim ws(1 To wsCount)
    ReDim wsColor(1 To wsCount)
    
    For I = 1 To wsCount
    
        Set ws(I) = Worksheets(I)
        wsColor(I) = ws(I).Tab.Color
        Set rngNow = rng(I + 1, 1)
        rngNow.Interior.Color = wsColor(I)
        rngNow = ws(I).Name
        
    Next I
    
End Sub

I am now trying to not just simply apply the values in ws(I).Name into their appropriate cells, but to make the sheet just a little more helpful by making the cell a clickable link.

I did some net searching and found a lot of what I read very hard to understand, but a fairly simple approach did seem to be to just make the cell a hyperlink with a display text, so I tried the following amendments -

Additional declarations

    Dim rngNowName As String
    Dim rngNowLink As String

Cell contents assignment line, i.e. rngNow = ws(I).Name replaced with

        rngNowName = ws(I).Name
        rngNowLink = "#" & rngNowName & "!A2"
        rngNow = Application.WorksheetFunction.Hyperlink(rngNowLink, rngNowName)

but I got a Run-time error 438 - Object doesn't support this property or method.

I did see online a thread that included the following code as the link, but I did not understand it, and could not see how to adapt it into my situation. If this, or similar, is the way I have to go, would the kind person who answers this plea for help, explain to me, in dummy speak, the purpose of each part of the command, and give me the correct version, using my variables, as shown above, for my situation.

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & wSheet.Name & "'" & "!A1", TextToDisplay:=wSheet.Name

With thanks in anticipation.

Philip
Bendigo, Victoria
Australia

 

[yky]

Try this:

rngNow = ws(I).Name
   [COLOR=#0000ff] ActiveSheet.Hyperlinks.Add Anchor:=rngNow, Address:="", SubAddress:= _
        "the-appropriate-sheet!A1", TextToDisplay:=rngNow.value[/COLOR]  [COLOR=#ff0000]<- What is the appropriate sheet? You need to fill it in. Something like "Sheet[/COLOR][COLOR=#ff0000]2"[/COLOR].

 

[cuddling101]

Thank you yky, that did it.

Best regards

Philip, Bendigo, Victoria, Australia