New Worksheet Hyperlinks

[Pacman52]

Hi all I'm hoping this is possible to achieve so wanted to ask if anyone could let me know firstly if its possible and if so how to do it.

So I have a 'main' worksheet. A user selects one of 5 different buttons and each button will open a relevant Userform for them to enter either a date or a name. Once they click the Ok button on the Userform, a copy of the relevant 'template' worksheet is copied as a new worksheet and renamed with whatever value they have entered on the Userform.

What I would like to add to the workbook on a worksheet called 'List', is a list of all the worksheets created and have them hyperlinked to the actual worksheet.

I know how to reference worksheets to a cell and add a hyperlink to them but this is when they already exist in the workbook but what I want to be able to do is 'automatically' add any subsequent worksheets added to the workbook on the next free row of the 'List' worksheet dynamically and add a hyperlink to it, as soon as they are created.

The actual workbook does have some VBA behind it although not much, (mainly for the Userforms) I really don't mind if a solution is through formulas or VBA, whichever works best.

So ultimately question would be, is this possible and if so how?

Thanks for any advice offered.

Paul

 

[Pacman52]

Hi Peter sorry about the link not working I've had problems with this happening before but thought I'd resolved it - obviously not !

So I've actually made a bit of progress after a bit of a YouTube search last night. I came across a channel run by Victor Chan and one of his episodes pretty much covers everything I needed including the hyperlinks.

I'm just adapting the code at the moment to be more specific to my workbook and testing it but so far so good.

When or if I do get it all working I will post the code up on this thread for you to see and hopefully it may help others along the way.

Paul

 

[Pacman52]

So after a bit of editing and jiggling around, I've finally got to a working solution on my initial question on this thread. The code below is my edited version but the full code is available on the link shown.

Firstly thank you so much Peter for your patience in trying to get this resolved for me but also credit must go to Victor Chan's YouTube channel, so if your not subscribed to his channel, I'd recommend it, as for me anyway, he explains the processes very clearly and gives explanations as to why he has done things in a certain way. It may not be useful to those who are already very knowledgeable on VBA but it will certainly help those who are learning and want to progress.

My Code:

Private Sub Worksheet_Activate()

    Application.ScreenUpdating = False
     
    Call Contents
     
    Application.ScreenUpdating = True
     
End Sub

Sub Contents()

    Const showHidden As Boolean = False
    Dim wsList As Worksheet
    Dim sheetIndex As Integer
    Dim rowNumber As Integer
    Dim colNumber As Integer
    Dim sheetCounter As Integer
     
    On Error Resume Next
    Set wsList = ThisWorkbook.Sheets("Contents")
    On Error GoTo 0
     
    If wsList Is Nothing Then
        Set wsList = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(2))
        wsList.Name = "Contents"
        
    Else
    
        wsList.Cells.Clear
        
    End If
     
    With wsList.Range("A3")
    
        .Value = "Contents"
        .Font.Bold = True
    
    End With
     
        rowNumber = 5
        colNumber = 1
        sheetCounter = 1
     
    
    For sheetIndex = 1 To ThisWorkbook.Sheets.Count
        With ThisWorkbook.Sheets(sheetIndex)

            If .Name <> "Contents" And _
                (showHidden Or .Visible = xlSheetVisible) Then
                 
                    wsList.Hyperlinks.Add _
                        Anchor:=wsList.Cells(rowNumber, colNumber), _
                        Address:="", _
                        SubAddress:="'" & _
                            ThisWorkbook.Sheets(sheetIndex).Name & _
                            "'!A1", _
                        TextToDisplay:=sheetCounter & ". " & _
                            ThisWorkbook.Sheets(sheetIndex).Name
                     
                    rowNumber = rowNumber + 1
                    sheetCounter = sheetCounter + 1

                 
        End If
        
        End With
        
    Next sheetIndex
    
End Sub

Link to Full Code:
List All Sheets in Excel: Formula and VBA Solutions (10 Levels)

 

[Peter_SSs]

Glad you have it working. Thanks for letting us know.