Macro/Hyper link to hidden sheet

[jitusodhi]

Hello All,

I'm stuck up with small issue.. I've 25 protected sheets in workbook, main sheet have the object button (rectangle box) created and hyper linked to their respective sheet.(sheets are renamed to something else)

Now the question is how would I link those button to the sheet when the sheet is hidden.(hyperlink does not work with sheet hidden)

I'm newbie with the VB's n Macros , please do let me know how to achieve it, all in layman terms please if possible.

thanks in advance!!

 

[AlphaFrog]

Right-click on each Rectangle and assign this macro . Remove the hyperlinks.

Change within the macro each Rectangle Name and the Sheet name you want to associate with each. You can add more Case statements. One for each Rectangle and its sheet.

[COLOR=darkblue]Sub[/COLOR] Rectangle_Click()
    
    [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
    
    [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] Application.Caller
        [COLOR=darkblue]Case[/COLOR] "[B]Rectangle 1[/B]": [COLOR=darkblue]Set[/COLOR] ws = Sheets("[B]Sheet1[/B]")
        [COLOR=darkblue]Case[/COLOR] "[B]Rectangle 2[/B]": [COLOR=darkblue]Set[/COLOR] ws = Sheets("[B]Sheet2[/B]")
        [COLOR=darkblue]Case[/COLOR] "[B]Rectangle 3[/B]": [COLOR=darkblue]Set[/COLOR] ws = Sheets("[B]Sheet3[/B]")
        [COLOR=darkblue]Case[/COLOR] "[B]Rectangle 4[/B]": [COLOR=darkblue]Set[/COLOR] ws = Sheets("[B]Sheet4[/B]")
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] ws [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        ws.Visible = [COLOR=darkblue]True[/COLOR]
        Application.Goto ws.Range("A1")
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Cross posted.
Macro/Hyper link to hidden sheet.

 

[jitusodhi]

I got Runtime error '13'
Type mismatch

when debugged
Case "Rectangle 1" gets highlighted in yellow.

Any idea what I did wrong.

Thanks!!

 

[AlphaFrog]

Make sure the rectangle name and the sheet name are correct. Otherwise show your whole code. Could be anything.

 

[jitusodhi]

Also when I click the rectangle it says the macro may not be available in this workbook or all macro is disable.
but I have enabled the macro in trust center.

Below is the code which is there...

Sub Rectangle_Click()

Dim ws As Worksheet

Select Case Application.Caller
Case "Rectangle 1": Set ws = Sheets("Sheet1")
Case "Rectangle 30": Set ws = Sheets("Sheet2")
Case "Rectangle 31": Set ws = Sheets("Sheet3")
Case "Rectangle 32": Set ws = Sheets("Sheet4")
End Select

If Not ws Is Nothing Then
ws.Visible = True
Application.Goto ws.Range("A1")
End If

End Sub
-----------------------------------------------------------