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 SubThis 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 SubIn 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
End Sub
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.
…praise indeed.! Thanks Franco…
Thank you.
Nice tutorial, but I cannot get the unHide to work.
Tried both versions but nothing happens.
The HideAgain worked swell..
Copied:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim shtName As String
‘shtName = Target.Name
shtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, “!”) – 1)
Sheets(shtName).Visible = xlSheetVisible
Sheets(shtName).Select
End Sub
To my sheet in VB, which then was hidden.
Any ideas?
BR Hans
It did the same for me, but I Found that removing the apostrophe from before and after the string name worked.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim desk As String
Dim shtname As String
Dim desk2 As String
shtname = Left(Target.SubAddress, InStr(1, Target.SubAddress, “!”) – 1)
‘MsgBox (shtname)
desk = Right(shtname, Len(shtname) – 1)
desk2 = Left(desk, Len(desk) – 1)
MsgBox (desk2)
Sheets(desk2).Visible = True
Sheets(desk2).Select
End Sub
I hope this is helpful for you too
Thanks Robin
This is great and works perfectly, thank you so much, however is it possible to do the same but have it work by clicking and image in the worksheet?
Sorry, Michael. the follow hyperlink event is not activated by links on shapes or images.
EDIT:
Is there a way to make this works with object hyperlinks.
I like to insert images; such as a home “icon” rather than the word home.
But for some reason this trick doesnt work.
Thanks
Sorry George, I don’t believe this is possible with objects such as shapes or images. The follow hyperlink event is not called.
WHAT IS THE SHTNAME WHEN I RUN THE VBA IT SAYS DEBUG AND SHOW THE LINE WITH YELLOW MARK
Sheets(ShtName).Visible = xlSheetVisible
HOW TO FIX THE PROBLEM
ShtName is the name of the sheet that the hyperlink was destined for. I cannot tell how to fix the problem. It may be that the sheet being linked to is not hidden.
Hi! Thank you so much, this was very helpful.
Question: Can I have two hyperlinks from different sheets to the same hidden one?
Thanks
You’re welcome, Fabiana. And yes, absolutely you can.
You would set the VBA code for each sheet with the hyperlink.
I believe that this is due to spacing in the sheet name, this code doesn’t like dashes or spaces.
I’m attempting to use this code with the hyperlink function and variable sheet names in my “main Sheet”, as the sheet name will change depending on what value is selected from a drop-down list elsewhere in the sheet, but nothing happens when I click on the hyperlink. I’ve attempted using both snippets of code provided, without any luck. Do you have any suggestions for how to modify this code to fit a variable sheet name?
I guess we could do it by testing the cell containing the drop-down list. When a hyperlink is clicked, check the value in that cell and if that sheet is hidden. then unhide etc as per the tutorial.
I followed the instructions and they work great using a hyper link to a hidden sheet and then hiding the sheet once I return back. I tried to use the same method of hyperlinking a shape, but did not have the same outcome using the same information. What changes to the macro if any needs to be done in order to make this work for a shape? An example would be Clicking on a shape named Sheet 2 then clicking on the shape to open up the hidden sheet 2. Any assistance is appreciated.
I’ve not done this Thomas, but believe the approach would be very different.
Hi, I used the =HYPERLINK(“#'”& Cell containing the sheet name &”‘!A1”, “XXXXXX”) excel function in one of the cells in my worksheet and applied the above method for that worksheet. Unfortunately, the above method does not seem to be working if the sheet is hidden and if I am using the =HYPERLINK function. May I know if you have a work around for this please?
thank you!
It will not work with the HYPERLINK function. I do not have a workaround for this, sorry.
Hey!
I have an IF statement that uses the HYPERLINK function to go to another tab but when the tab is hidden it doesn’t work. i tried your solution above but since it is in a IF statement it doesn’t work. is there something else that needs to be done in VBA?
Here is the IF statement =IF(F8=””,””,HYPERLINK(“#LO1a!E15″,”Check Answer”))
Hi Alex, I don’t believe this technique can be used with the HYPERLINK function.
I cannot get either of these to work.
Clicking the link does nothing and the sheet remains hidden. If I unhide the sheet manually the hyperlink works.
Please help, I’ve tried both versions of the code provided
brilliant – both bits of code worked instantly and are proving extremely useful.
many many thanks
Excellent! Great to hear, Robert.
Both parts of the coding worked great! I do have one question about adding other hyperlinks to the same page.
If I am wanting to add a hyperlink that takes you to an external web address, such as “Google.com”, how do you successfully add it to the same sheet without getting the Runtime error ‘5’? I believe it is being caused by the hidden sheet code, but I’m not sure how to fix it. I’m trying to avoid having to use an entire new sheet for this one hyperlink.
Would I be able to place this code in the “ThisWorkbook” section so it applies to all the sheets? The reason I ask is because I have a macro that is running to delete sheets and recreates them.
Yes, there is a SheetFollowHyperlink procedure that can be used for the ThisWorkbook section.
Hi,
Output after code:
shtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, “!”) – 1)
is:
‘shtName’ – I still had ” ‘ ” character, therefore macro wasn’t working.
I’ve added code:
shtName = Replace(shtName, “‘”, “”)
Then my output was exactly the same as tab name. Worth checking if someone had the problem with this.
Any chance you could show the entire Macro as you have modified it?
Hi there, trying the second bit of code here and have tried both entering manually and copy and pasting
Dim ShtName As String
ShtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, “!”) – 1)
Sheets(ShtName).Visible = xlSheetVisible
Sheets(ShtName).Select
but I keep getting an out of range error relating back to the third line.
Any ideas what it may be
Hi Andy,
This is probably due to the sheet name containing a space. I didn’t think of this when constructing the article. When a sheet has a space it contains single quotes around its name like ‘Polar Bear’!.
The code would need to remove the quotes before doing the LEFT function.
We would probably do this and assign to the ShtName variable
I used:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim ShtName As String
ShtName = Target.Name
Sheets(ShtName).Visible = xlSheetVisible
Sheets(ShtName).Select
End Sub
Private Sub Worksheet_Activate()
On Error Resume Next
Sheets(ActiveCell.Value2).Visible = False
End Sub
And it working, but prior to opening the tab it launches an error message that says:
“Reference isn’t valid”
Once I click OK, the code works and opens the tab and then when I leave it closes.
How do I stop the “Reference isn’t valid” pop up from appearing?
Hi! Thanks so much for this fix!
I mostly had success with your code, but hit a snag and hoped you could help! I used your second option for making the sheet visible. I have many links on the same sheet going to their own separate destinations. At first, I did not notice an issue because the first two links on that page worked perfectly. However, when I went to test the third link and the others after it, I am getting the error:
Run-time error ‘9’: Subscript out of range
Thanks again!
Hi Suzanne, for those links, does the sheet name contain a space?
I am getting a
“Run-time error ‘9’:
Subscript out of range” error.
Please advise. Thanks!
Triple check that the sheet name and text match exactly. If they definitely do, does the sheet name contain a space?
Great information. I don’t know anything about micros but when I followed the example it hid my sheets.
Is there a way to re-hide the sheets by clicking on a cell and not have them automatically hide? An example is I have a Dashboard and I want to open 5 out of 10 sheets. With this example if I leave the once hidden sheet it hides again.
Thank you all
Hello, first I want to thank you for the immense help. How can I do to insert a hyperlink to another worksheet, as I only had success in one folder.
Is it also possible to limit this to a certain selection? I have a main page where certain cells and columns contain hyperlinks to sheets that are visible. And a selection of certain cells and columns that link to hidden sheets.
Now I get an error when I click on a hyperlink pointing to a visible sheet.
For info!
If you have sheet names with a space,
e.g. ‘Daily Totals’ you need to replace the space with an underscore.
Daily Totals -> Daily_Totals
That should help with
“Run-time error ‘9’:
Subscript out of range”.
Hope this helps
Awesome dude.. thanks for this.. totally worked
I’m having the same problem, the code works fine until I got to the sheetname with a space. can you provide the code needed to resolve the issue please?