Is there a way to add a Pivot Data sheet back button when clicking into the pivot table?

Question

I have a workbook which has several things in place to hide the sheet tabs and protect certain things hidden in the workbook.

I have pivot tables in the workbook and I want to allow the user to see the pivot table data if they double click in the data table.

However when they do this due to the Tabs being hidden it doesnt allow them to go back to the previous sheet and instead it keeps them locked on that page.

I was hoping i could create a simple code that fires when a new sheet tab is created to drop a shape with a hyperlink back to the home page (or previous page if possible) at the top of the sheet then allowing the user to navigate back.

Any assistance would be great.

I found some information and part of a code that I have put together:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
   ActiveSheet.Shapes.AddShape(msoShapeRoundedRectangle, 27, 14.25, 72, 71.75).Select
    
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim hyperLinkedShape As Shape
    Set hyperLinkedShape = ws.Shapes("Rectangle 1")

    ws.Hyperlinks.Add Anchor:=hyperLinkedShape, Address:="", _
        SubAddress:="Sheet1", ScreenTip:=""
End Sub

but i cannot get the hyperlink to attach to the shape. Is there something im missing?

hope this makes sense.

thanks in advance.

Answer 1

Please try.

Option Explicit

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Sh.Shapes.AddShape(msoShapeRoundedRectangle, 27, 14.25, 72, 71.75).Select

    Sh.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:="", _
        SubAddress:="Sheet1!A1", ScreenTip:="Back"
        
    Sh.Cells(1, 1).Select ' optional
End Sub