This VBA code controls the visibility of worksheets based on hyperlink navigation. When a hyperlink is clicked, the Workbook_SheetFollowHyperlink event makes the destination sheet visible and navigates to it, while the current sheet remains visible. The Workbook_SheetDeactivate event hides any sheet that is deactivated unless it is a sheet explicitly excluded (such as Sheet1). This ensures that specific sheets, like Sheet1, remain visible while others are hidden upon deactivation.

The expression ThisWorkbook.Worksheets(Array(Sheet1.Name)) is used to reference specific sheets by dynamically retrieving their names. In this case, it fetches the name of the worksheet object Sheet1 and uses it to create an array of sheet names. This allows you to easily refer to the worksheet even if its name changes in the future. By using the Sheet1 object (the code name of the worksheet), you ensure that the reference remains valid regardless of what the user renames the sheet to in Excel.

Using the sheet’s code name (Sheet1) is a safer and more reliable method than hardcoding its name (e.g., “Sheet1”). The code name of a sheet does not change when the user renames the sheet through the Excel interface, whereas the sheet name (the visible name) can be altered. This ensures that the code continues to function correctly even if the user modifies the sheet’s name.

Refactored Code

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    Dim Destination As Range
    Rem Use error handling to prevent the code from stopping if there is an issue with the hyperlink
    On Error Resume Next
    
    Rem Set the destination range based on the hyperlink's target address (SubAddress)
    Set Destination = Range(Target.SubAddress)
    
    Rem Reset error handling back to default
    On Error GoTo 0
        
    Rem If the destination is valid (not Nothing), proceed with the following
    If Not Destination Is Nothing Then
        
        Rem Make the sheet where the hyperlink leads visible
        Destination.Parent.Visible = xlSheetVisible
        
        Rem Navigate to the destination range
        Application.Goto Destination
    End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Dim Worksheet As Worksheet
    
    Rem Loop through each worksheet in the workbook except for the active one (Sh)
    For Each Worksheet In ThisWorkbook.Worksheets(Array(Sheet1.Name))
        
        Rem If the current sheet is the active one (Sh), exit the sub to avoid hiding it
        If Worksheet Is Sh Then Exit Sub
    Next
    
    Rem If the sheet is not the active one, hide it when deactivating
    Sh.Visible = xlSheetVeryHidden
End Sub

Assuming you have cell reference hyperlinks on the sheet1

you need to use this code:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
  Dim sa As String
  sa = Target.SubAddress
  sa = Left$(sa, InStr(sa, "!") - 1)
  If Left$(sa, 1) = "'" Then sa = Mid$(sa, 2, Len(sa) - 2)
  With ThisWorkbook.Sheets(sa)
    .Visible = xlSheetVisible
    .Activate
  End With
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    ' Hide the sheet again when you leave it
    If Sh.Name <> "sheet1" Then ' use the exact sheet name here
        Sh.Visible = xlSheetVeryHidden
    End If
End Sub

P. S. I depicted my test workbook where sheets are Sheet1, Sheet2, and Sheet3. But in code I used the sheet name (“sheet1”) provided by TS.