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

 

[Peter_SSs]

It is definitely possible.

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.

You would add some code to the code that does this. The extra code would use the name of the new worksheet added and add the hyperlink to the bottom of the list in the 'List' worksheet.
If you need help with the additional code then it would be useful to see the existing code so that we could more easily add to it. I assume that the codes for the 5(?) existing userforms is reasonably similar. If not perhaps for a start give us two of the codes that are quite different.

What column in 'List' will/does contain the hyperlinks to the various sheets?

 

[Pacman52]

Hi Peter, thanks so much for the reply, in short I'd love some help with the code that does this as everything I've tried so far doesn't work but that is more than likely down to my inexperience with VBA.

As you mentioned correctly, each sub is basically the same the only differences are the worksheet names and tab colours.

So the code I have that copies the template worksheet is:

Sub TestNewWS()

Dim WSCount As Long
WSCount = Worksheets.Count

        ActiveWorkbook.Sheets("Template").Copy _
        After:=ActiveWorkbook.Sheets(WSCount)
        ActiveSheet.Name = Sheets("Dates").Range("C3").Value
        ActiveSheet.Tab.Color = RGB(78, 167, 46)

End Sub

This sub is called from the 'OK' command button on whatever userform has been selected and the newly copied worksheet name is referenced from a worksheet called "Dates" and 'C3' (or whatever cell is relevant) gets it's value from the userform textbox.

The column on the List worksheet currently is 1 with the first row currently being 3, although I haven't added any sort of 'design' to the List worksheet yet.

Not sure its relevant or not but I do have a formula in 'B2' of the template worksheet to get the worksheet name so it can be used as a title on any new worksheets created.

=MID(CELL("filename",B1),FIND("]",CELL("filename",B1))+1,50)

Many thanks Paul

 

[Peter_SSs]

Try adding this blue code

Sub TestNewWS()

Dim WSCount As Long
WSCount = Worksheets.Count

        ActiveWorkbook.Sheets("Template").Copy After:=ActiveWorkbook.Sheets(WSCount)
        ActiveSheet.Name = Sheets("Dates").Range("C3").Value
        ActiveSheet.Tab.Color = RGB(78, 167, 46)
        
        With Sheets("List").Range("A" & Rows.Count).End(xlUp).Offset(1)
          .Hyperlinks.Add Anchor:=.Cells(1), Address:="", SubAddress:="'" & Range("B2").Value & "'!A1", TextToDisplay:=Range("B2").Value
        End With
End Sub

 

[Pacman52]

Thanks Peter but sadly that errored out with 'Application / Object defined' on the line

.Hyperlinks.Add Anchor:=.Cells(1), Address:="", SubAddress:="'" & Range("B2").Value & "'!A1", TextToDisplay:=Range("B2").Value

 

[Peter_SSs]

It worked for me so there must be something different about your file compared to mine. I can't really guess what that might be.
Can you upload a copy of the file (any sensitive data disguised or removed) to DropBox, OneDrive or Google Drive etc and provide a public shared link here?

 

[Pacman52]

I've no issues uploading the workbook as it hasn't got any data in it yet but before I do that I've just noticed that the template worksheet names all have a space between them i.e. "Test Template" and I do remember reading somewhere this could be what is causing the error my side.

I will see what happens if I rename a worksheet without spaces in the name and post back the results.

 

[Pacman52]

Just an update renaming the worksheet with no spaces didn't make any difference. I will sort out sharing the workbook in a moment.

 

[Pacman52]

Hi Peter hopefully this link works but if not just let me know - thanks Paul

https://drive.google.com/file/d/13JMyXlZrNhrfeNwYo5xu0xqtRNaYJXsI/view?usp=drive_link

 

[Peter_SSs]

hopefully this link works but if not just let me know

I'm afraid that it doesn't.