VBA to open hyperlink in a hidden sheet which also containing hyperlink

[ErynK]

Hi, i have a workbook containing plenty sheets.
I would like to set the sheet2 @ Overview as my dashboard. Which, when i click link in Overview, it will unhide sheet linked and naviagate to it. Then, hide that opened sheet when i go go back to Overview.

I found a macros fulfilling the above needs perfectly.

However, i would like to get help to get a code that can achieve the below approach:
1. Let assumes the Overview dashboard sheet is A.
2. When i click link in A, it will unhide & redirect me to the corresponded hidden sheet(Assumes its B).
3. When i click on hyperlink in B, it will also unhide and redirects me to the hidden sheet C.
4. When i go back to A, the rest of the sheets will hide again.

Perhaps there were someone here can help or guide me through to achieve the above solutions.
Ps: I was completely new to the macros coding. Hence it is appreciated if you are willing to share the code to me directly or can teach me how to code it. I'm eager to learn

Thanks in advanced!

 

[ErynK]

This is the code as attached in the picture:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
            If Sh.CodeName <> "Sheet2" Then
               Sh.Visible = xlSheetVeryHidden
            End If
        End Sub

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
            Sheetname = VBA.Replace(Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1), "'", "")
            Sheets(Sheetname).Visible = True
            'Sheets(Sheetname).Activate
            Application.EnableEvents = False
            Target.Follow
            Application.EnableEvents = True
End Sub

 

[ErynK]

Hi, i have a workbook containing plenty sheets.
I would like to set the sheet2 @ Overview as my dashboard. Which, when i click link in Overview, it will unhide sheet linked and naviagate to it. Then, hide that opened sheet when i go go back to Overview.

I found a macros fulfilling the above needs perfectly.

However, i would like to get help to get a code that can achieve the below approach:
1. Let assumes the Overview dashboard sheet is A.
2. When i click link in A, it will unhide & redirect me to the corresponded hidden sheet(Assumes its B).
3. When i click on hyperlink in B, it will also unhide and redirects me to the hidden sheet C.
4. When i go back to A, the rest of the sheets will hide again.

View attachment 87109

Perhaps there were someone here can help or guide me through to achieve the above solutions.
Ps: I was completely new to the macros coding. Hence it is appreciated if you are willing to share the code to me directly or can teach me how to code it. I'm eager to learn

Thanks in advanced!

Hmm i think the above request is actually achieved. I tried clicking on hyperlink in C and that links actually worked to navigate me to the corresponding hidden sheet without issue.

Now let me correct my issue:
I'm actually also set shape containing hyperlink. From what i read and study, the code i was now using is not applicable for shape a.k.a button.

If i wishes to use VBA to unhide and redirect to that particular hidden sheet via shape, i saw some people suggest to right click on the shape and assign new macro that are redirecting me to the specific locations.

Which, it means location that the hyperlink address i use previously no longer worked. Intead, i has to repeat this assign macro step for every single of this shape.

Long story short, is there any where round to skip that procedure while kept that shape button worked? I have a lots shape applied so it will be a lots job to replacing a new macro to each of them