Microsoft Excel allows you to add hyperlinks to hidden worksheets that are under the mainsheet on your document file. For example, if you have a workbook which contains multiple worksheets, the first master sheet has hyperlinks to other sheets, and now, all the sheets are hidden except for the first one. If you have links to the hidden sheets they will not open as the corresponding sheet has been hidden from view, therefore not accessible. If you need to add a link to a hidden page, this can be done via coad on the main sheets command area. In any case, the hyperlinks which link to hidden sheets are not available. But, how could you make these hyperlinks work successfully? When you click one hyperlink, follow the steps below to see how.
Step by step process – How to hyperlink to hidden sheets in excel?:
- Go to the excel document.
- Navigate to the main sheet in the document.
- Now right click on the main sheet.
- Then click on “View code”.
- Open a new script panel by double clicking the main sheet.
- After that change the “General” tab to “Worksheet”
- Input the code below between and script intro and “End sub”.
- Now click on “File” > “Close and return to Microsoft Excel”.
- Now when you click on the linked cell it will open the hidden sheet.
| linkto = Target.SubAddress wherebang = InStr(1, linkto, “!”) If wherebang > 0 Then mysheet = Left(linkto, wherebang – 1) Worksheets(mysheet).Visible = True Worksheets(mysheet).Select myaddr = Mid(linkto, wherebang + 1) Worksheets(mysheet).Range(myaddr).Select End If |
Things to consider when hyprlinking hidden sheets
- The process illustrated above will only work on Windows, there physically isnt a script available for Windows.
- Hidden sheets will display again, however you will need to manually hide them again.
- Make sure you save the file as a Macro enabled sheets, otherwise the script wont work.
When you type out the code ensure the “If Target.Column = 1 Then” has the corresponding numerical figure to the sheet column in which the linked cell resides. For instance if the linked cell is in column 2 (B) then change the “1” into a “2”. This way the code will be easy for the Excel index task to run its necessary outputs to open the hidden link.
This process will allow you to keep the file hidden and the content available via a hyperlink on the main master sheet. You will need to ensure the corresponding cell is linked with the hidden sheet in question.
‘ShtName’ is a string variable declared in this code. The name of the clicked hyperlink (the text displayed by the hyperlink) is then assigned to that variable. This is done with the help of a variable called Target.
Because the linked text and the names of the worksheets are the same in this case, this is a smart idea. If the hyperlink text and worksheet name are different, you should use the second example.
Macros are often used to process data in a worksheet. Unless the worksheet containing the cell is hidden, your macro may access any cell in the workbook. When a worksheet is hidden, it is also hidden from macro operations.
It’s also worth noting that the Selection object (and its Select function) aren’t the only ways to get information. If you work with ranges instead (using the Range method or creating an object using the Range method), you can quickly access data on a hidden worksheet without having to make it visible.
In depth Step by step process – How to hyperlink to hidden sheets in excel?:
Step by step breakdown:
- Go to the excel document where the hidden sheets reside.
- Navigate to the main sheet in the document.
- Now right click on the main sheet.
- Then click on “View code”.
- Open a new script panel by double clicking the main sheet.
- After that change the “General” tab to “Worksheet”
- Change the next column to “Followhyperlink”
- Input the code below between and script intro and “End sub”.
- Type out the code shown below. This will allow you to hyperlink to hidden sheets.
| linkto = Target.SubAddress wherebang = InStr(1, linkto, “!”) If wherebang > 0 Then mysheet = Left(linkto, wherebang – 1) Worksheets(mysheet).Visible = True Worksheets(mysheet).Select myaddr = Mid(linkto, wherebang + 1) Worksheets(mysheet).Range(myaddr).Select End If |
- Now click on “File” > “Close and return to Microsoft Excel”.
- Now when you click on the linked cell it will open the hidden sheet.
That’s it for this Blog thank you for taking time out to read our content, please feel free to email our team about how it went if you followed the steps or if you need more help with the questions we answered in this Blog.