How to follow hyperlink to hidden sheet in Excel?
If you have a workbook with multiple worksheets and want to use hyperlinks on a master sheet to navigate to hidden sheets, you'll notice the hyperlinks become inactive once the sheets are hidden. To resolve this, you can use VBA code to ensure that clicking a hyperlink automatically opens the corresponding hidden sheet. This article provides step-by-step instructions to achieve this functionality and even offers an option to re-hide the sheet after you return to the master sheet.
Follow hyperlink to open hidden sheet with VBA code
Follow hyperlink to open hidden sheet and then hide it again with VBA code
Follow hyperlink to open hidden sheet with VBA code
The following VBA code may help you to open the hidden worksheet when clicking its relative hyperlink, please do as this:
1. Right click the sheet tab that you want to follow the hyperlink to hidden sheet, and then choose View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, copy and paste the following code into the blank module:
VBA code: Follow hyperlink to open hidden sheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice
Application.ScreenUpdating = False
On Error Resume Next
If Target.Column = 1 Then
Sheets(Target.Value).Visible = xlSheetVisible
Sheets(Target.Value).Select
End If
Application.ScreenUpdating = True
End Sub
Note: In the above code, the number 1 in this If Target.Column = 1 Then script indicates the column number which contains the hyperlinks, please change it your need.
2. Then save and close this code window, now, when you click a hyperlink that links to a specific hidden sheet, the sheet will open immediately.
Follow hyperlink to open hidden sheet and then hide it again with VBA code
Sometimes, you need to follow the hyperlink to open the hidden sheet, and when you go back the master sheet, you want the opened sheet to be hidden again. The below VBA code can do you a favor:
1. Right click the sheet tab that you want to follow the hyperlink to hidden sheet, and then choose View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, copy and paste the following code into the blank module:
VBA code: Follow hyperlink to open hidden sheet when go back hide it again:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
'Updateby Extendoffice
Application.ScreenUpdating = False
Dim strLinkSheet As String
If InStr(Target.Parent, "!") > 0 Then
strLinkSheet = Left(Target.Parent, InStr(1, Target.Parent, "!") - 1)
Else
strLinkSheet = Target.Parent
End If
Sheets(strLinkSheet).Visible = True
Sheets(strLinkSheet).Select
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Activate()
On Error Resume Next
Sheets(ActiveCell.Value2).Visible = False
End Sub
2. Then save ad clos the code window, when you click the hyperlink, the linked hidden sheet will be opened immediately, but, if you go back the master sheet which contains the hyperlinks, the opened sheet will be hidden automatically again.
Note: These codes only apply to sheet names that match the hyperlink text.
Demo: Follow hyperlink to open hidden sheet with VBA code
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite ā Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license ā set up in minutes (MSI-ready)
- Works better together ā streamlined productivity across Office apps
- 30-day full-featured trial ā no registration, no credit card
- Best value ā save vs buying individual add-in