Excel VBA hyperlink to unhide hidden and very hidden sheets

Question

I am trying to unhide sheets from a hyperlink in excel but I am not able to get it to work.

I want to only have one sheet visible at all times and I want the hyperlink to be in a merged cell and not only in a regular cell.

When I click a back button on the unhidden sub sheet I want the start page to become visible.

Ex. I am on the start page and I click a link to SubSheet1 only SubSheet1 is visible. I click the back button and only StartPage is visible.

https://www.mrexcel.com/forum/excel-questions/976686-hyper-link-object-hidden-sheet.html

I found this code on the web:

Sub switchToSheet(fromSht As Worksheet, toShtName As String)
    Dim sht As Worksheet

    Set sht = ThisWorkbook.Worksheets(toShtName)
    sht.Visible = xlSheetVisible
    fromSht.Visible = xlSheetHidden
    sht.Activate
    sht.Range("B1").Select
End Sub

Sub allSheetsVisible()
    Dim sht As Worksheet

    For Each sht In ThisWorkbook.Worksheets
        sht.Visible = xlSheetVisible
    Next sht
End Sub

I put that in an module.

Then

I put this into thisWorkbook

Private Sub Workbook_Open()

    Dim sht         As Worksheet
    Const master    As String = "Master"

    With Me.Worksheets(master)
        .Visible = xlSheetVisible
        .Activate
        .Range("B1").Select
    End With

    For Each sht In Me.Worksheets
        If sht.Name <> master Then
            sht.Visible = xlSheetHidden
        End If
    Next sht

End Sub

In my StartPage I have the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim adr As String

    adr = Target.Address(0, 0)

    Select Case adr
        Case "A1"
            switchToSheet Me, "Sub1"
        Case "A2"
            switchToSheet Me, "Sub2"
    End Select
End Sub

Where I have a hyperlink in cell A1 and A2. This code however doesn't work with merged cells!

Answer 1

Instead of hyperlinks maybe it is better to use Shapes and by right click assign macro to them.

About the macro: 1. If the names of the worksheets in Your workbook are constant then You can just assign proper macros to hide and unhide worksheets: a) To unhide:

Sub Unhide()
    ActiveWorkbook.Worksheets("nameOfWs").Visible = True
End Sub

b) To hide:

Sub Hide()
    ActiveWorkbook.Worksheets("nameOfWs").Visible = xlSheetVeryHidden
End Sub

2. To make this more dynamic You can test if worksheets has something, e. g. specific color, value in cell etc. then You can make Your sub more "clever". To loop through all worksheets, You can use e. g.:

   Sub LoopAll()

   Dim ws As Worksheet

   For Each ws In ActiveWorkbook.Worksheets 'some code e. g. hide/unhide basing on Ifs Next ws

   End Sub

And now all is in Your hands to combine abive code to what You need.