I know this is late, but I figured out why the Runtime Error 9 was happening for some people when they were trying to make their sheet visible. When you use Target.SubAddress, it returns your sheet name with single quotes instead of double quotes. You need to add a line to replace those. Addrow.ca helped me with this. See code below: Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) LinkTo = Target.SubAddress WhereBang = InStr(3, LinkTo, "!") If WhereBang > 0 Then MySheet = Left(LinkTo, WhereBang - 2) MySheet = Replace(MySheet, "'", "")
Worksheets(MySheet).Visible = True Worksheets(MySheet).Select MyAddr = Mid(LinkTo, WhereBang + 1) Worksheets(MySheet).Range(MyAddr).Select End If
Hi Bill, this is excel poetry to my ears! I am so impressed !! This would have taken me months to work out. I searched on google for weeks - on one had any code that worked. The best part is the sheet will hide automatically. This is my number 1 excel must have for any one dealing with more than 10sheets. Can I say I officially love you well done xo
Hi!...I have a excel file with conditional hyperlink to 30 different worksheets in same workbook, but when I hide those 30 sheets, the hyperlink is not working. Can anybody help me with that. I can share a test file with you. -Himadri Sen
Nope. but sorted it with this code....need to share in 2 parts...Cheers from Australia! (Part 1) Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Dim strSheet As String If InStr(Target.Parent, "!") > 0 Then strSheet = Left(Target.Parent, InStr(1, Target.Parent, "!") - 1) Else strSheet = Target.Parent End If Sheets(strSheet).Visible = True Sheets(strSheet).Select End Sub
Hey Bill, thanks for the insightful video, very clearly explained and great little trick, have it working now! Just one question, this same code won't work for Shapes (I.e. a "Shape" created to act as a button, with the hyperlink on the Shape)...any idea why this is? Thanks
Hi Bill. Great video but I have a problem. Once the hidden tab is open, when I hit back it goes to the menu but does not hide again. Please help me. Thanks
How to solve the 'Runtime error 9: Subscript out of range' issue: make sure the worksheet you're referring to has no space or special characters (like "&") in the name, then it should work fine
Thanks Bill!! This completely worked. I'm pretty good w/ Excel, but have never messed around w/ VBA or macros too much. It's certainly makes me a stronger excel user, but my new job would rather I stick to software packages that do this type of work (Jobvite) rather than hook up an excel spreadsheet with macros. Shame.
Bill thanks for the tutorial. I have the same type of setup on my workbook but my hyperlinks are added to shapes I have put within the cell to give it a nice three dimensional look. Is there any way to use the same concept in this tutorial to work with shapes?
I try it and this is very helpful. But I need more advice Mr Jelen, I'm a new in VB and working with multiple worksheets of my excel dashboard, and I used the AutoShape to linking the sheets. From MrExcel forum I got the code below: Sub GoToSheet() With Worksheets(Application.Caller) .Visible = True .Select End With End Sub Sub BackToWS() Dim aSheet As String aSheet = ActiveSheet.Name Worksheets("Menu").Select Worksheets(aSheet).Visible = False End Sub The problem is: There are no a parent sheet, In each sheet there are some AutoShape which link to another sheets. e.g: From Sheet1, I can go to Sheet2 or Sheet3, but at Sheet3 I could not go back to Sheet2. The second code could not activate the hidden sheet. Above code only works when a workbook has a parent sheet. Urgent help and Thanks lot advance
+Mawardi A. ASJA maaf pak bisa ajarkan saya saya menggunakan autoshape seperti yang bapak pakai ... Bisa saya diberi penjelasan untuk penyelesaianya? Terimakasih
What if you want to have two links, one back to menu and one to another sheet? Now you have hardcoded "Menu" and it will allways link to that sheet no matter what. TY!
Super helpful video! I've used this code multiple times and it works great. I'm having trouble with a main menu(A) and sub menus (B&C). "A" contains links to "B" & "C". "B" & "C" contain multiple hyperlinks that unhide/hide like they should. I cant get "B" & "C" to hide when I used a return link back to "A". Hope you can help!
Hi Bill, I have watched this video over and over (so happy to find it as now I know what I would like to do actually works), but not for me. I have followed your code and it just won't work. I don't even get an error message. I'm using Windows XP and Excel 2010. Any ideas.... When you say to "Watch" Target, I don't get option to open up Target and check...
i know this is an ancient video lol but im trying to do this exact thing in excel right now. and i was unable to really understand. i tried using the exact code you showed in the video but it didnt work and i think its because the code you showed was only reference code obviously because you are unable to tell us the exact code to use because our sheet names and such are different than yours. and i think thats where the "wherebang" thing came from as a way to reference sheet names and such, but im just not sure i fully understood. i will play around with it some more and try to figure it out. im sure i will eventually get it
Hey Bill, I would like to thank you for everything you've already taught me. I look forward to receiving an autographed copy of the '40 Greatest Excel Tricks' book ! - Alex Hav
Wouldn't it be much easier to hide every sheet but the Menu-sheet using the worksheet activate event on Menu sheet? Thanks for the tip for the Watch-window!
Bill, when I originally worked on my workbook with this Macro it worked fine, and the changes are still there. It will not let me continue to hyperlink to hidden workbooks. I am unsure of what to do from here.
Hi Bill, Thanks for your help, all thing work fine for me, but when i click in thev "menu" sheet the sheets still in the sheets bar. Its work only if i click in the back cells.. Can find solution for that to let also the user if he click in the menu all sheets shall be hidden. Thanks in advance.
When I am in the VBA window, I click on the Sheet and both dropdowns do not have anything else listed. (General) does not have Worksheet, same as (Declarations) . How do I get those dropdowns to show?
Bill, first off, this is a great and very helpful video! I have two separate sheets that both hyperlink to a sheet. Is there a way, when i hit the back hyperlink that it will take me to sheet i was previously on? Instead of entering the name of a worksheet (Worksheets("MySheetName").Select") for the code on the "back" hyperlink; is there code that will remember which sheet i was on when i hyperlinked to that sheet, and take me back to it? Thanks!
Hey Bill. Can I add a wrinkle to this amazing little trick that? I have the sheets working correctly, but now I want to link each of my hidden sheets to pdfs (CVs specifically) in the same folder as the spreadsheet. But when I do that (bc/ it's a link), the spreadsheet returns to the menu. Is there a way to have one link (return to menu) hide the sheet, while other links (to CVs) leave the sheet open?
Is there a way to select the first empty cell from a column with this code instead of always going to cell A1 when clicking the hyperlink? For example, by adding Cells(Rows.Count,1).End(xlUp).Row + 1 Thanks. Thanks!
I have tried to save the file in those formats and when I do it deletes the workbook and makes it unusable, added the same formula on VBA and still not working ... I just dont know what im doing wrong .. help
Hi Bill, really appreciate your video. Would you help me to fix the issue? I did exact same but when I tried, the Error with Compile error: Invalid use of property shows
Im facing issue with hidden part 2 And other sheets as having message with error 438 ! Also giving message with ( object doesnt support this property or method
I have a multiple sheets created by Data validation(drop down), need to create code for unhide sheets using Hyperlink or dropdown box, Can you help on this.
Hi Bill, what if my hyperlinks are in an object instead (for my case, I inserted a shape and applied the hyperlink there). I tried your codes but nothing happens. I tried the cell hyperlink and it worked. Appreciate if you can provide a codes that can cater to object hyperlinks as well...thanks in advance
Hi Bill, Love this tutorial. It's been really useful and I've learnt a lot. I am just wondering how could I use a shape, such as the Bevel shape to link my sheets together like this? I am following the coding and hyperlinking, but it doesn't seem to work. Do they have to be macros? If so, I have no clue what to do!!! Thanks for any help
Love the help! I can get the code to work to use hyperlinked text to open a hidden sheet, however I'm having difficulty geting the code to work with "hyperlinked" objects such as a picture or wordart to a hidden sheet. HELP!
Hi Bill Thanks for this. Same as some comments below I am getting Run-time error 9: Subscript out of range. I have the following code - am I missing something else? WhereBang = InStr(1, LinkTo, "!") If WhereBang > 0 Then MySheet = Left(LinkTo, WhereBang - 1) MySheet = Application.WorksheetFunction.Substitute(MySheet, "", "") Worksheets(MySheet).Visible = True Worksheets(MySheet).Select MyAddr = Mid(LinkTo, WhereBang + 1) Worksheets(MySheet).Range(MyAddr).Select
This worked very well, thank you. VBA only works when Excel is opened in App. Is there any way to get the same result using Office Script so that it works online?
Hi Bill, This code works on Windows only. Do you know a way this can be achieved on Mac Excel? As I am having troubles hyperlinking to a hidden worksheet.
Hi Bill, i just wanted to ask if u already have a solution or anyone can hyperlink using hyperlink() with regards to this post? I have the same issue hope you find out a soon.
Hi bill I wonder if you can help me, I have a spread sheet using the code you describe above and it woks perfectly in my test sheet. How ever the sheet I want to use this in creates its own hyperlinks using the =HYPERLINK() function and for some reason you code will not work when the link is created in this way. any help would be much appreciated.
Thanks Bill, great video. But i have a question, on the back hyperlink, what if i dont want it to always go back to main menu, i have other worksheets. Can't i just have the back button address just go to that worksheet that i specify? Do i just change the back hyperlink vba "main menu" to the specific worksheet? Then the main menu hyperlink, i just put that vba code on the worksheet where i want it to start from. Sorry i hope i didn't confuse you.
Hi Thanks for helpful VBA everything works very fine except the hide unused sheet when I clicked on back the sheet still appeared, and there is and pomp messaged with run-time error '424'" object required. any help on that please
Hello bill, what would i need to do to hide the menu sheet whilst on another worksheet? allowing the only way back to the menu to be the hyperlink ensuring the page is rehidden many thanks
Check that your file is saved with an .XLSM or .XLSB extension. If you left the file with the default .XLSX extension you will get this exact experience that you described.
@@MrXL I have tried to save the file in those formats and when I do it deletes the workbook and makes it unusable, added the same formula on VBA and still not working ... I just dont know what im doing wrong .. help
@@TheChico create new file and save it as macro enabled. are you using "save as"? perhaps your links are not pointing to your new file but the old one. this macro should definitely not delete your workbooks, it just hides and unhides sheets
Are you in a corporate environment? Ask your Information Technology department if they have set Group Policy to prevent anyone from using XLSM files or if they have an aggressive anti-virus policy that deletes xlsm files. If they say yes, then suggest that they should also ban any gasoline engines in the parking lot. People should remove the engine from their car and push the car to work. By disallowing macros, they are essentially preventing you and your co-workers from being efficient.
Hi Bill, how do you recommend changing the macro when the worksheet title is only numbers? For instance, my worksheets are named 1, 2, 3, etc. The worksheet names automatically become i.e. '1', which is making the macro not work. Thank you
Hi Bill, Im just starting to learn how to use VBA, im not sure which of your per-configured string commands need to be changed if my main sheet is called "Dashboard" where my hyperlink in cell B5 is found which is called "report1" that links me to sheet 2 which is named 2014. Tried following your per-configured commands to the T but Im still unsuccessful. :(
Bill, God Bless you :P :D...Hey Bill, The first code works for my hidden sheet but to go back and hide the same sheet, the code isnt working. Secondly i have 2 hyperlinks on my worksheet, one is for back and other is for forward. what should i do then?
***** I got the VB code from MS community source. It is working now. I found that once i click the cell which is having the hyperlink, it opens the hidden sheet, but once again i need to hide the sheet, each time i open, i need to hide the sheet, any way i can keep it hidden? I notice that i could not able to do with the hyperlink connected to a object. Any code to make it work for objects, basically i will have a menu sheet with rectangular object (Insert-object-shape-basic shapes) that will have hyperlink to hidden sheet, i want the reader to click on the object to see the hidden sheet and back to menu sheet to navigate to another sheet by clicking another object-hyperlink, while he doing so, all the sheets are always hidden Any possible code for the above situation?
Hi Bill After I wrote the code; I pressed the hyperlink cell. I got "Run-time error '438' : Object does not support this property or method". When I express the debug button it leads to a part of the code highlighted in yellow which is " Worksheets(MySheet).Visisble = True". What do you think the problem is.
Interesting. We can troubleshoot this. Just after the line of code that starts MySheet = LEFT, add the following line: MsgBox MySheet & VBLF & LinkTo & VBLF & WhereBang Run the code. Click a hyperlink. A box will appear with the name of the sheet, the hyperlink address, and where they think the exclamation point is. Let me know what you get there. (After clicking OK to dismiss this, you will still get the runtime error 9.)
@@husseinsalloukh1882 When you get the Debug error, hover over MySheet and look closely at the value stored in that variable. The name has to match one of your worksheets exactly. Look for something like the variable says Jan-2021 and the variable is January-2021.
Hi Bill I wonder if you can help me, I have a spread sheet using the code you describe above and I cannot get the second part of the code to work. I get a runtime error '438': object does not support the property or method. The code is as follows: Private Sub Worksheet_SelectionChange (ByVal Target As Range) Worksheets("Menu").Select Target.Parent.Worksheet.Visible = False End Sub Any help is appreciated.
I have an error. When I do the same things in this video,it works fine. But I close the files and open back my hyperlink didn't work and i don't know how to find my hide worksheets back.
Check these things 1) is the workbook saved with the default XLSX file extension? That file type deletes all macros. You need to make sure to save as XLSM or XLSB. 2) check macro security. It can’t be at the top setting (disable all macros without notification). You have to choose the second item. 3) when the workbook opens, you have to choose to Enable Macros (this either displays in the message bar above the grid or in a dialog).
@@MrXL I save the workbook as macro-enable and my settings are exactly what you talk but i still have this error and i don't know how to solve. May be my desktop is the problem ?
I don't know how to hyperlink to textbox or picture. But that does not mean it can not be done. Post your question to a new thread here: www.mrexcel.com/board/forums/excel-questions.10/
It works! But now I have another problem: *It is not working in sheets with spaces* How do I solve this issue? I tried putting quotes like this ("MySheet") but it didn't work I am not sure if that's where I'm supposed to put those quotes though Please help me out!😣
Hi Bill - thanks for the vid. I have successfully hyperlinked everything. However, if I send my excel workbook (and the folder with all data used) to someone else, the hyperlinks do not work. PLEASE could you guide me here!?
Hi Bill, Every time i try to run the script I get an error "Run-Time error'9'" when I get to : Worksheets(MySheet).Visible = True Do you have any thoughts?
I do not see any misspellings anywhere. When I put the MsgBox code in my values are as follows WhereBang: 23 Linkto: '88TH Vehicle Exhaust'!A1 MySheet: '88thVehicle Exhaust' Below is my complete Code: Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) 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 End Sub
Hi Bill, I need your help, yourPodcast really helpes for one of my workbooks, but however I actually need a VBA code that would open multiple sheets on clicking a single hyperlink instead of one hyperlink one sheet, would that be possible? I don't know anything about writing codes..
Thanks Bill, but I actually have a master sheet with 23 hyperlinks, and each of these hyperlinks are to open a specific number of hidden worksheets. Will the above code work on this? Thanks again.
Hi, I'm not really sure what is wrong, I typed in everything exactly the same and I'm getting this error: "Compile Error: Block If without End If" and it puts a highlight on the very first line for Following the Hyperlink..
I put that in and now it gives me an error saying that "the Sub or Function is not defined" ? highlights the first line again.. Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) 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 End Sub
Is there a way to view hidden worksheets that I can't enter a password for? My workbook has a password to open but it is hiding content from as though I'm not the author. I've tried VBA codes from other YT videos without success.
Hey Bill, thanks for the great video! Really helpful for workbooks with big amount of sheets. May I get your help with something I would like to do? I have a big matrix with data, which should be taken by different sheets. But the sheets shall be approx. 100, so I would like to create a macro for it.
Myrto Ananida that sounds too complicated for a short video. Can you register at this site and post a new thread with complete details www.mrexcel.com/forum/index.php
Hey Bill, Whenever I try to run the code, it gives me a debug error on; MySheet = Appication.WorksheetFunction.Substitute(MySheet, "", "") If I remove this, i get debug error on; Worksheets(MySheet).Visible = True Kindly help
I enjoy vba as much the next guy. But when I send Excel files to others, I can never be sure that they enable the macros. So I use hyperlinks and then hide the sheet tabs by using settings.
Now I know that it´s because of the =HYPERLINK formula, I´ve tried debuggin with formula and without formula, and the hyperlinks created by the formula does not call the event followHyperlink, dunno how to continue D;
Is there a way for me to implement additional hyperlinks within the same worksheet of the workbook? I have a menu page that lists all the applicable sheets. By clicking home on each worksheet, the sheet will hide and return to home. What if I want a worksheet to include an additional hyperlink. For example, A1 has a home hyperlink, clicking that will close the sheet and return to menu. A2 has a hyperlink leading to another sheet within the workbook. Is that possible?
I tried that, which I should've mentioned... So using the three pages as an example Home Chevrolet Suburban Home has all the manufacturers listed. So if I click on Chevrolet it will open Chevrolet which lists all models, clicking on each model should open a new sheet based on the selected model. Instead, it bounces back to home and hides Chevrolet. However, adding if Target.Name "Home" then Exit Sub This works if all pages are not hidden. So if Chevrolet is not hidden, I can click from Home > Chevrolet. If it is hidden it doesn't respond. Same for others, say for instance I'm on Chevrolet, Link will open Suburban only if it is active, if it's hidden - Nothing.
That's still giving me the same issue. It won't open a hidden page. Adding the code, I can Chevrolet from home, and Suburban from Chevrolet if it's not hidden. If it's hidden nothing happens.
Okay, I got it to respond to the hidden pages. However, The sub page "Suburban" won't go back to hidden if I click back to Chevrolet or Home. What could be the solution to that?
No was just a logical error. I had a spelling error for the "Back to Chevrolet" I corrected that. My buttons are only the name of the manufacturer, like "Chevrolet".
I know this is late, but I figured out why the Runtime Error 9 was happening for some people when they were trying to make their sheet visible. When you use Target.SubAddress, it returns your sheet name with single quotes instead of double quotes. You need to add a line to replace those. Addrow.ca helped me with this. See code below:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
LinkTo = Target.SubAddress
WhereBang = InStr(3, LinkTo, "!")
If WhereBang > 0 Then
MySheet = Left(LinkTo, WhereBang - 2)
MySheet = Replace(MySheet, "'", "")
Worksheets(MySheet).Visible = True
Worksheets(MySheet).Select
MyAddr = Mid(LinkTo, WhereBang + 1)
Worksheets(MySheet).Range(MyAddr).Select
End If
End Sub
A couple changes on these lines: WhereBang = InStr(1, LinkTo, "!")
, MySheet = Left(LinkTo, WhereBang - 1)
Thank you so much for identifying this with a solution:)
Thanks for this!
you are a star
Hi Bill, this is excel poetry to my ears! I am so impressed !! This would have taken me months to work out. I searched on google for weeks - on one had any code that worked. The best part is the sheet will hide automatically. This is my number 1 excel must have for any one dealing with more than 10sheets. Can I say I officially love you well done xo
Hi!...I have a excel file with conditional hyperlink to 30 different worksheets in same workbook, but when I hide those 30 sheets, the hyperlink is not working. Can anybody help me with that. I can share a test file with you.
-Himadri Sen
Nope. but sorted it with this code....need to share in 2 parts...Cheers from Australia!
(Part 1)
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim strSheet As String
If InStr(Target.Parent, "!") > 0 Then
strSheet = Left(Target.Parent, InStr(1, Target.Parent, "!") - 1)
Else
strSheet = Target.Parent
End If
Sheets(strSheet).Visible = True
Sheets(strSheet).Select
End Sub
Thanks a lot for this video. Just in time. I had like 20+ sheets and I dont want them to be crowded on my tabs.
So helpful. Makes me realise just how much more there is to learn.
Hey Bill, thanks for the insightful video, very clearly explained and great little trick, have it working now!
Just one question, this same code won't work for Shapes (I.e. a "Shape" created to act as a button, with the hyperlink on the Shape)...any idea why this is?
Thanks
Hi Bill. Great video but I have a problem. Once the hidden tab is open, when I hit back it goes to the menu but does not hide again. Please help me. Thanks
I'm having the same prob. Can you teach us how? I'm using excel 2016.
The "Stop" command is very useful!! Thank you so much!!!
A great use of the "Watch" window, a very forgotten part of VBA.
How to solve the 'Runtime error 9: Subscript out of range' issue:
make sure the worksheet you're referring to has no space or special characters (like "&") in the name, then it should work fine
This worked perfectly! I never really comment on videos, but I had to just say THANK YOU SO MUCH, this was amazing!
Thanks Bill!! This completely worked. I'm pretty good w/ Excel, but have never messed around w/ VBA or macros too much. It's certainly makes me a stronger excel user, but my new job would rather I stick to software packages that do this type of work (Jobvite) rather than hook up an excel spreadsheet with macros. Shame.
Bill thanks for the tutorial. I have the same type of setup on my workbook but my hyperlinks are added to shapes I have put within the cell to give it a nice three dimensional look. Is there any way to use the same concept in this tutorial to work with shapes?
I try it and this is very helpful.
But I need more advice Mr Jelen,
I'm a new in VB and working with multiple worksheets of my excel dashboard, and I used the AutoShape to linking the sheets.
From MrExcel forum I got the code below:
Sub GoToSheet()
With Worksheets(Application.Caller)
.Visible = True
.Select
End With
End Sub
Sub BackToWS()
Dim aSheet As String
aSheet = ActiveSheet.Name
Worksheets("Menu").Select
Worksheets(aSheet).Visible = False
End Sub
The problem is: There are no a parent sheet, In each sheet there are some AutoShape which link to another sheets. e.g: From Sheet1, I can go to Sheet2 or Sheet3, but at Sheet3 I could not go back to Sheet2. The second code could not activate the hidden sheet. Above code only works when a workbook has a parent sheet.
Urgent help and Thanks lot advance
*****
thanks for the suggestion Mr Jelen
I will try this way..
***** Great sir, it is work properly..
Thanks lot
+Mawardi A. ASJA maaf pak bisa ajarkan saya saya menggunakan autoshape seperti yang bapak pakai ... Bisa saya diberi penjelasan untuk penyelesaianya? Terimakasih
What if you want to have two links, one back to menu and one to another sheet? Now you have hardcoded "Menu" and it will allways link to that sheet no matter what. TY!
Super helpful video! I've used this code multiple times and it works great.
I'm having trouble with a main menu(A) and sub menus (B&C). "A" contains links to "B" & "C". "B" & "C" contain multiple hyperlinks that unhide/hide like they should. I cant get "B" & "C" to hide when I used a return link back to "A". Hope you can help!
Thanks for the quick response, i will give it a try and let you know how it goes!
Thanks again!
Hi Bill, I have watched this video over and over (so happy to find it as now I know what I would like to do actually works), but not for me. I have followed your code and it just won't work. I don't even get an error message. I'm using Windows XP and Excel 2010. Any ideas.... When you say to "Watch" Target, I don't get option to open up Target and check...
Worksheets(MySheet).Visible = True is always wrong. Any Help?
You might have spaces in your sheet's name
Same error. No spaces. Its exactly as it is in the video
i know this is an ancient video lol but im trying to do this exact thing in excel right now. and i was unable to really understand. i tried using the exact code you showed in the video but it didnt work and i think its because the code you showed was only reference code obviously because you are unable to tell us the exact code to use because our sheet names and such are different than yours. and i think thats where the "wherebang" thing came from as a way to reference sheet names and such, but im just not sure i fully understood. i will play around with it some more and try to figure it out. im sure i will eventually get it
Thank You Very Much Sir :)
Even if, I have no idea about excel, but still I can follow your instructions.
You helped me a lot.
Hey Bill, I would like to thank you for everything you've already taught me. I look forward to receiving an autographed copy of the '40 Greatest Excel Tricks' book !
- Alex Hav
Wouldn't it be much easier to hide every sheet but the Menu-sheet using the worksheet activate event on Menu sheet?
Thanks for the tip for the Watch-window!
Bill, when I originally worked on my workbook with this Macro it worked fine, and the changes are still there. It will not let me continue to hyperlink to hidden workbooks. I am unsure of what to do from here.
Great video!
WOW! Thanks for explaining the code step by step.
Hi Bill,
Thanks for your help, all thing work fine for me, but when i click in thev "menu" sheet the sheets still in the sheets bar. Its work only if i click in the back cells.. Can find solution for that to let also the user if he click in the menu all sheets shall be hidden.
Thanks in advance.
Sir when I try the addwatch target there is no + sign beside the shades logo.
In the drop down (General), worksheet is not available? How do I make this visible so I can begin writing my first usable macro?
When I am in the VBA window, I click on the Sheet and both dropdowns do not have anything else listed. (General) does not have Worksheet, same as (Declarations) . How do I get those dropdowns to show?
Bill, first off, this is a great and very helpful video!
I have two separate sheets that both hyperlink to a sheet. Is there a way, when i hit the back hyperlink that it will take me to sheet i was previously on? Instead of entering the name of a worksheet (Worksheets("MySheetName").Select") for the code on the "back" hyperlink; is there code that will remember which sheet i was on when i hyperlinked to that sheet, and take me back to it?
Thanks!
Is it possible to make the sheets hide again after clicking back??
Hey Bill. Can I add a wrinkle to this amazing little trick that? I have the sheets working correctly, but now I want to link each of my hidden sheets to pdfs (CVs specifically) in the same folder as the spreadsheet. But when I do that (bc/ it's a link), the spreadsheet returns to the menu. Is there a way to have one link (return to menu) hide the sheet, while other links (to CVs) leave the sheet open?
is there a way to write code to use a Hyperlink List as a dropdown list rather than have a big list of links or buttons?
Hi Bill, when I tried to apply for the hyperlinking button, things do not work. What I can do please
Is there a way to select the first empty cell from a column with this code instead of always going to cell A1 when clicking the hyperlink? For example, by adding Cells(Rows.Count,1).End(xlUp).Row + 1
Thanks.
Thanks!
I have tried to save the file in those formats and when I do it deletes the workbook and makes it unusable, added the same formula on VBA and still not working ... I just dont know what im doing wrong .. help
Hi Bill, really appreciate your video. Would you help me to fix the issue? I did exact same but when I tried, the Error with Compile error: Invalid use of property shows
Can We Use Worksheet 1's Activate Event To Hide All The Sheets After Following The Hyperlink? For Each... Next Loop
Its working great, Thanks Bill.
Im facing issue with hidden part 2
And other sheets as having message with error 438 !
Also giving message with ( object doesnt support this property or method
How to navigate the command button with multiple sheets hidden using excel?
I have a multiple sheets created by Data validation(drop down), need to create code for unhide sheets using Hyperlink or dropdown box, Can you help on this.
For all those stuck with an error at Worksheets (My sheet). visible = true. Try visible = HIDDEN. Worked for me 😁
Hi Bill, what if my hyperlinks are in an object instead (for my case, I inserted a shape and applied the hyperlink there). I tried your codes but nothing happens. I tried the cell hyperlink and it worked. Appreciate if you can provide a codes that can cater to object hyperlinks as well...thanks in advance
same problem
Can I hide tooltips ("Click once to follow the hyperlink....") with VBA?
Hi Bill,
Love this tutorial. It's been really useful and I've learnt a lot. I am just wondering how could I use a shape, such as the Bevel shape to link my sheets together like this? I am following the coding and hyperlinking, but it doesn't seem to work. Do they have to be macros? If so, I have no clue what to do!!!
Thanks for any help
Love the help!
I can get the code to work to use hyperlinked text to open a hidden sheet, however I'm having difficulty geting the code to work with "hyperlinked" objects such as a picture or wordart to a hidden sheet.
HELP!
Hi Bill
Thanks for this. Same as some comments below I am getting Run-time error 9: Subscript out of range. I have the following code - am I missing something else?
WhereBang = InStr(1, LinkTo, "!")
If WhereBang > 0 Then
MySheet = Left(LinkTo, WhereBang - 1)
MySheet = Application.WorksheetFunction.Substitute(MySheet, "", "")
Worksheets(MySheet).Visible = True
Worksheets(MySheet).Select
MyAddr = Mid(LinkTo, WhereBang + 1)
Worksheets(MySheet).Range(MyAddr).Select
This worked very well, thank you. VBA only works when Excel is opened in App. Is there any way to get the same result using Office Script so that it works online?
I don't know TypeScript well enough to know if this is possible. I suspect it is, but you need to find someone with some TypeScript experience.
@@MrXL Thank you for your response.
Is it possible to build Macro for the Back function on each spreadsheet to have a button instead of a link?
Hi Bill, This code works on Windows only. Do you know a way this can be achieved on Mac Excel? As I am having troubles hyperlinking to a hidden worksheet.
Hi Bill, i just wanted to ask if u already have a solution or anyone can hyperlink using hyperlink() with regards to this post? I have the same issue hope you find out a soon.
Hi bill I wonder if you can help me, I have a spread sheet using the code you describe above and it woks perfectly in my test sheet. How ever the sheet I want to use this in creates its own hyperlinks using the =HYPERLINK() function and for some reason you code will not work when the link is created in this way. any help would be much appreciated.
Thanks Bill, great video. But i have a question, on the back hyperlink, what if i dont want it to always go back to main menu, i have other worksheets. Can't i just have the back button address just go to that worksheet that i specify? Do i just change the back hyperlink vba "main menu" to the specific worksheet? Then the main menu hyperlink, i just put that vba code on the worksheet where i want it to start from. Sorry i hope i didn't confuse you.
Bill Jelen: thx Bill, i will give a try, if i have any issues i will get back with you.
Hi Thanks for helpful VBA
everything works very fine except the hide unused sheet when I clicked on back the sheet still appeared, and there is and pomp messaged with run-time error '424'" object required.
any help on that please
Thank you, I got it.
Absolutely fantastic.
Great trick Bill. Thanks
Hello bill, what would i need to do to hide the menu sheet whilst on another worksheet? allowing the only way back to the menu to be the hyperlink ensuring the page is rehidden
many thanks
Hi, I'm having trouble returning to the main page... Do I need to enter any code on the other pages to be able to return? Thanks again
Hey Bill, It was working, but when I opened my sheet again after closing, its not working
Check that your file is saved with an .XLSM or .XLSB extension. If you left the file with the default .XLSX extension you will get this exact experience that you described.
@@MrXL I have tried to save the file in those formats and when I do it deletes the workbook and makes it unusable, added the same formula on VBA and still not working ... I just dont know what im doing wrong .. help
@@TheChico
create new file and save it as macro enabled. are you using "save as"?
perhaps your links are not pointing to your new file but the old one. this macro should definitely not delete your workbooks, it just hides and unhides sheets
Are you in a corporate environment? Ask your Information Technology department if they have set Group Policy to prevent anyone from using XLSM files or if they have an aggressive anti-virus policy that deletes xlsm files. If they say yes, then suggest that they should also ban any gasoline engines in the parking lot. People should remove the engine from their car and push the car to work. By disallowing macros, they are essentially preventing you and your co-workers from being efficient.
Great! Thanks for the fast reply.
Hi Bill, how do you recommend changing the macro when the worksheet title is only numbers? For instance, my worksheets are named 1, 2, 3, etc. The worksheet names automatically become i.e. '1', which is making the macro not work. Thank you
Hi Bill,
Im just starting to learn how to use VBA, im not sure which of your per-configured string commands need to be changed if my main sheet is called "Dashboard" where my hyperlink in cell B5 is found which is called "report1" that links me to sheet 2 which is named 2014. Tried following your per-configured commands to the T but Im still unsuccessful. :(
this Already 1 year or more, but still work. thank you
Well done, excellent tutorial, thank you.
Hi Bill. Great video. I tried and most of the sheets work but then 4 sheets gave me a runtime error '9' subscript out of range. Please help
Same here.
See latest comment. Might be too late now but there is a solution.
Hi, I've followed this trick to the letter and keep getting the same error. Run-time error '9' Subscript out of range. Any ideas?
Don't worry, I worked it out - I missed the apostrophe from "".
Thanks - all good!
i need formula for drop down working hyperlinks, is it possible to do without vba???
Hey, thanks for this vedio, I execute the program but when I add new work sheet error come Run-Time error 9
Subscript out of range
I got the same error. Did you manage to fix this? If so then please let me know how
thank you
Bill, God Bless you :P :D...Hey Bill, The first code works for my hidden sheet but to go back and hide the same sheet, the code isnt working. Secondly i have 2 hyperlinks on my worksheet, one is for back and other is for forward. what should i do then?
Amazing and very helpful!..thanks
I am getting error code "1004" Select method of Range class failed
Please help
I could not get follow hyperlink command in the menu window of VB code editor. How to get it?
*****
I got the VB code from MS community source. It is working now. I found that once i click the cell which is having the hyperlink, it opens the hidden sheet, but once again i need to hide the sheet, each time i open, i need to hide the sheet, any way i can keep it hidden?
I notice that i could not able to do with the hyperlink connected to a object. Any code to make it work for objects, basically i will have a menu sheet with rectangular object (Insert-object-shape-basic shapes) that will have hyperlink to hidden sheet, i want the reader to click on the object to see the hidden sheet and back to menu sheet to navigate to another sheet by clicking another object-hyperlink, while he doing so, all the sheets are always hidden
Any possible code for the above situation?
Hi Bill
After I wrote the code; I pressed the hyperlink cell. I got "Run-time error '438' : Object does not support this property or method". When I express the debug button it leads to a part of the code highlighted in yellow which is " Worksheets(MySheet).Visisble = True". What do you think the problem is.
Thank you Bill. Yes you are right.
Hi Bill, I am getting the same error where it doesn't like Worksheets(MySheet).Visible = True, I get the error 9.
Please could you help?
Interesting. We can troubleshoot this. Just after the line of code that starts MySheet = LEFT, add the following line:
MsgBox MySheet & VBLF & LinkTo & VBLF & WhereBang
Run the code. Click a hyperlink. A box will appear with the name of the sheet, the hyperlink address, and where they think the exclamation point is.
Let me know what you get there.
(After clicking OK to dismiss this, you will still get the runtime error 9.)
Same error: “Run time error ‘9’ subscript out of range”. The debug stops on “worksheets(MySheet).Visible = True”. PLEASE HELP
@@husseinsalloukh1882 When you get the Debug error, hover over MySheet and look closely at the value stored in that variable. The name has to match one of your worksheets exactly. Look for something like the variable says Jan-2021 and the variable is January-2021.
Hi.. I still cant get it right. Dont v followhyperlink option. Can you help? Tq
Hi sir how can i fix it debug
Worksheets (MySheet).Visible = True
Please h3lp me
Me too 😢
Hi Bill I wonder if you can help me, I have a spread sheet using the code you describe above and I cannot get the second part of the code to work. I get a runtime error '438': object does not support the property or method. The code is as follows:
Private Sub Worksheet_SelectionChange (ByVal Target As Range)
Worksheets("Menu").Select
Target.Parent.Worksheet.Visible = False
End Sub
Any help is appreciated.
Thank you Bill. Very Kind of you.
Hi Bill i am getting Runtime error 9 : Subscript out of range. How to resolve this issue?
Hello Bill I am getting the same error have tried all the tricks from the comments. could you please help me y M i getting runtime error
I have an error. When I do the same things in this video,it works fine. But I close the files and open back my hyperlink didn't work and i don't know how to find my hide worksheets back.
Check these things
1) is the workbook saved with the default XLSX file extension? That file type deletes all macros. You need to make sure to save as XLSM or XLSB.
2) check macro security. It can’t be at the top setting (disable all macros without notification). You have to choose the second item.
3) when the workbook opens, you have to choose to Enable Macros (this either displays in the message bar above the grid or in a dialog).
@@MrXL I save the workbook as macro-enable and my settings are exactly what you talk but i still have this error and i don't know how to solve. May be my desktop is the problem ?
Hello.. how to hyperlink hidden sheets to textbox or pictures? Is it possible? Hope you read this
I don't know how to hyperlink to textbox or picture. But that does not mean it can not be done. Post your question to a new thread here: www.mrexcel.com/board/forums/excel-questions.10/
It works! But now I have another problem:
*It is not working in sheets with spaces*
How do I solve this issue? I tried putting quotes like this ("MySheet") but it didn't work I am not sure if that's where I'm supposed to put those quotes though
Please help me out!😣
Hi Bill - thanks for the vid. I have successfully hyperlinked everything. However, if I send my excel workbook (and the folder with all data used) to someone else, the hyperlinks do not work. PLEASE could you guide me here!?
Bill Jelen Thanks for the reply. It works using another PC, however on a Mac it seems to be a different story. I will try what you have recommended
Bill Jelen still no luck. It is a Mac problem (and unfortunately I need it work on a Mac).
Bill Jelen All Macros have been enabled. Seems to be a more complicated reason. Sitting with my brother who has used Python before. We are stuck ☹️
Thanks, bill - really appreciate the help. The compromise is just disabling the tab toolbar and leave out the coding.
Hi Bill,
Every time i try to run the script I get an error "Run-Time error'9'" when I get to :
Worksheets(MySheet).Visible = True
Do you have any thoughts?
I do not see any misspellings anywhere. When I put the MsgBox code in my values are as follows
WhereBang: 23
Linkto: '88TH Vehicle Exhaust'!A1
MySheet: '88thVehicle Exhaust'
Below is my complete Code:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
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
End Sub
Hi Bill, I need your help, yourPodcast really helpes for one of my workbooks, but however I actually need a VBA code that would open multiple sheets on clicking a single hyperlink instead of one hyperlink one sheet, would that be possible? I don't know anything about writing codes..
Thanks Bill, but I actually have a master sheet with 23 hyperlinks, and each of these hyperlinks are to open a specific number of hidden worksheets. Will the above code work on this? Thanks again.
OMG!! It works perfect!! Thanks a lot Bill, you're a star :D
Hi, what if the link is in the object of the worksheet, may we know the code to use? Thank you.
same issue here
Hi, I'm not really sure what is wrong, I typed in everything exactly the same and I'm getting this error: "Compile Error: Block If without End If" and it puts a highlight on the very first line for Following the Hyperlink..
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
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).SelectEnd Sub
The Add Watch window doesn't pull anything up when I try to do that for Target in the first line
I put that in and now it gives me an error saying that "the Sub or Function is not defined" ? highlights the first line again..
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
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
End Sub
Is there a way to view hidden worksheets that I can't enter a password for? My workbook has a password to open but it is hiding content from as though I'm not the author. I've tried VBA codes from other YT videos without success.
Hey Bill, thanks for the great video! Really helpful for workbooks with big amount of sheets.
May I get your help with something I would like to do?
I have a big matrix with data, which should be taken by different sheets. But the sheets shall be approx. 100, so I would like to create a macro for it.
Myrto Ananida that sounds too complicated for a short video. Can you register at this site and post a new thread with complete details www.mrexcel.com/forum/index.php
God Bless you Bill :)
Hey Bill,
Whenever I try to run the code, it gives me a debug error on;
MySheet = Appication.WorksheetFunction.Substitute(MySheet, "", "")
If I remove this, i get debug error on;
Worksheets(MySheet).Visible = True
Kindly help
Hi Bill all ok I worked it out, did not have 'Followhyperlink' selected. Doh! ;-)
I enjoy vba as much the next guy. But when I send Excel files to others, I can never be sure that they enable the macros. So I use hyperlinks and then hide the sheet tabs by using settings.
Hi Mr. Excel, I´ve done this and it does not work for me, actually I click the hyperlink and it just does nothin, what can it be?
Now I know that it´s because of the =HYPERLINK formula, I´ve tried debuggin with formula and without formula, and the hyperlinks created by the formula does not call the event followHyperlink, dunno how to continue D;
Is there a way for me to implement additional hyperlinks within the same worksheet of the workbook? I have a menu page that lists all the applicable sheets. By clicking home on each worksheet, the sheet will hide and return to home. What if I want a worksheet to include an additional hyperlink. For example, A1 has a home hyperlink, clicking that will close the sheet and return to menu. A2 has a hyperlink leading to another sheet within the workbook. Is that possible?
I tried that, which I should've mentioned...
So using the three pages as an example
Home
Chevrolet
Suburban
Home has all the manufacturers listed. So if I click on Chevrolet it will open Chevrolet which lists all models, clicking on each model should open a new sheet based on the selected model. Instead, it bounces back to home and hides Chevrolet.
However, adding
if Target.Name "Home" then Exit Sub
This works if all pages are not hidden. So if Chevrolet is not hidden, I can click from Home > Chevrolet. If it is hidden it doesn't respond. Same for others, say for instance I'm on Chevrolet, Link will open Suburban only if it is active, if it's hidden - Nothing.
That's still giving me the same issue. It won't open a hidden page. Adding the code, I can Chevrolet from home, and Suburban from Chevrolet if it's not hidden. If it's hidden nothing happens.
Okay, I got it to respond to the hidden pages. However, The sub page "Suburban" won't go back to hidden if I click back to Chevrolet or Home. What could be the solution to that?
Nevermind. I think I got it to work. If I come across anything, I'll post. This is awesome! Thank You!
No was just a logical error. I had a spelling error for the "Back to Chevrolet" I corrected that. My buttons are only the name of the manufacturer, like "Chevrolet".
I need help . can you the same type of function in google spread sheet.
Linkto=target.subaddress is not working i dont know why