Creating a hyperlink to another worksheet in Excel is easy. But if that worksheet is hidden then the hyperlink will not work. In this tutorial we provide a solution to follow a hyperlink to a hidden worksheet.
The example is that we have a “Main” worksheet with hyperlinks to the 4 other sheets of the workbook.
In the image below, you can see that the “Apples” sheet is hidden.
This is going to take a little Excel VBA to get this working. In this tutorial I will show two ways of making this hyperlink work, even when the sheet is hidden.
If you are a beginner in Excel VBA, why not speed track your learning by enrolling in the Excel VBA for beginners course.
Watch the Video – Hyperlink to a Hidden Worksheet
Excel VBA to Follow Hyperlink to Hidden Worksheet
Lets begin by opening the Visual Basic Editor by clicking Visual Basic on the Developer tab, or by pressing Alt + F11.
We need to use this code on the Worksheet_FollowHyperlink event so that it occurs when the link is clicked. We need to open the code window for the “Main” worksheet because that is where the event resides.
Do this by double clicking on the sheet in the Project Explorer window. The Title will display the currently active code window.
To access the Worksheet_FollowHyperlink event, select Worksheet from the Object list, and then FollowHyperlink from the Procedures list.
We can now enter some code inside the Worksheet_FollowHyperlink sub provided.
What is quite misleading is the name of this event. It is called FollowHyperlink but it does not actually follow the link. It is just an event that is triggered by someone clicking a hyperlink on that sheet.
We will need to use code to identify what link was clicked, unhide the required sheet, and take the user to it.
For the first example, the code below will do the trick.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Dim ShtName As String ShtName = Target.Name Sheets(ShtName).Visible = xlSheetVisible Sheets(ShtName).Select End Sub
This code declares a string variable named ShtName. It then assigns the name of the clicked hyperlink (the text displayed by the hyperlink) to that variable. A variable named Target is provided to us for this.
In this example the hyperlink text and the name of the worksheets is exactly the same, so this is a good idea. If the hyperlink text and worksheet name are different, then the second example is better for you.
This is then used to make the sheet visible and then select it.
This second example of code does not rely on the link text and sheet names matching and is more durable.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Dim ShtName As String ShtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1) Sheets(ShtName).Visible = xlSheetVisible Sheets(ShtName).Select End Sub
In this example we extracted the sheet name from the sub address of the clicked hyperlink. A sub address is when you link to a location within the current file.
This was done by extracting all the characters up to the exclamation mark in the sub address. This is because exclamation marks separate the sheet name and cell address or range in Excel i.e. Sheet3!D2.
The Left function was used for this, and the Instr function located the position of the exclamation mark.
Hiding the Sheet Again When Leaving
Now that solves our problem and the purpose of this tutorial. However, you might want the sheet to automatically hide itself again when you have finished with it.
You could be leaving the sheet by following a hyperlink on that sheet, or maybe just by clicking a sheet tab at the bottom. I want some code to run, no matter the method of leaving.
This code will be placed in the Deactivate event of each worksheet.
Start by double clicking on a sheet in the Project Explorer window to open its code window.
Select Worksheet from the Object list (just like previously in this tutorial) and then Deactivate from the Procedures list.
And enter the line below inside the sub.
Private Sub Worksheet_Deactivate()
Me.Visible = xlSheetHidden
This code refers to itself as Me. Nice simple code to make the sheet hide itself on the deactivation of the sheet.
Excel VBA enables you to take control when Excel tasks don’t quite work how you need them to, like in this example of following a hyperlink to a hidden worksheet. It is a fantastic skill to have.