Macro/Hyper link to hidden sheet

jitusodhi

New Member
Joined
Jan 18, 2014
Messages
3
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!!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,383
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.

Code:
[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

New Member
Joined
Jan 18, 2014
Messages
3
I got Runtime error '13'
Type mismatch

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

Any idea what I did wrong.

Thanks!!
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,383
Make sure the rectangle name and the sheet name are correct. Otherwise show your whole code. Could be anything.
 

jitusodhi

New Member
Joined
Jan 18, 2014
Messages
3
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
-----------------------------------------------------------

2niazxd.jpg
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,394
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top