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
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!...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
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?
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 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.
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 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.
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
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
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
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!
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.
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.
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.
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.
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
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
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
***** 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?
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.
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;
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, 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. :(
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, 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, 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
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
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
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 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/
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, 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 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.
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!?
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, Im having a problem, so from the hidden one (say number 2) that was linked to the main sheet(1), i also added another hyperlink to another hidden file(3). However, now even when i try moving forward from (2-->3) its closing 2 and sending me back to the main 1 while still opening 3 but not putting me straight on it i hope im not confusing you.
What im trying to say is after clicking the first hyperlink, and reaching tab (2), i want tab (2) to hide after Both going back to tab (1) or moving forward to tab (3)
Hi again Bill, one more question, is it possible to do this, but from a drop down list hyperlinked to a hidden sheet? I have the lists named in sheet 1, and the drop down list and the "go" button on page 2 and i want to go to page 3. Any ideas?
i created the named ranges on the "main" and created hyperlinks there to sheets 2,3,4,5,etc...then on "sheet 1" i created the drop down list using data validation, in the cell next to the drop down list i wrote the function hyperlink("[document name]main"!&A12,"Go") to activate the hyperlink, but when i hide the sheets and i try to go to the hyperlink it doesnt work and i tried adapting the code u wrote now it didnt work. Do i need to write any code in the "main" sheet, or do i only write the code u just suggested on the general pressed the go button of sheet 1 (which has the drop down list)?
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.
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".
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...
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.
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.
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 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
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, 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?
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, 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.
Hey Bill,I run this macro and continually get the runtime error `9`Subscript out of range, and when I debug it highlights this line:Worksheets(MySheet).Visible = Trueis there something wrong with this? The hyperlinks are not on the first sheet in the workbook, does that have something to do with it?Below is the whole code and I can`t see what it is that I am doing wrong.Please helpPrivate 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
Thanks Bill, what ended up being a solution for me was that I needed to eliminate any spaces between the words. Ended up just renaming them Income_Statement.
In case I did not mention it in the video, let me say it here: This only works from hyperlinks attached to cells, not hyperlinks attached to objects. Hyperlinks from cells don't look as nice as hyperlinks from objects, but we all had to suck it up and switch to ugly hyperlinks if you want to have the VBA code to run.
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!😣
Do you see the Developer tab in the Ribbon? Click on Developer, Macro Security. Is the Windows 10 computer set to the top choice of "Disable all Macros Without Notification"? If it is, change it to the same choice that you are using in Windows 7. In case you do not see the Developer tab, try this: (1) Right-Click the Ribbon and choose Customize Ribbon. (2) Look along the list box on the right side for Developer and check the box next to that tab. (3) Click OK.
![CAMPปลิ้น | EP.83[2/2] ประสบการณ์ขนหัวลุกจากตำนานวงการผี!](http://i.ytimg.com/vi/T_kFecQw7Ds/mqdefault.jpg)
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
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
So helpful. Makes me realise just how much more there is to learn.
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
A great use of the "Watch" window, a very forgotten part of VBA.
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.
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?
The "Stop" command is very useful!! Thank you so much!!!
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.
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.
This worked perfectly! I never really comment on videos, but I had to just say THANK YOU SO MUCH, this was amazing!
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 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.
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?
Hi Bill, when I tried to apply for the hyperlinking button, things do not work. What I can do please
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
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
Great video!
I am getting error code "1004" Select method of Range class failed
Please help
Is it possible to make the sheets hide again after clicking back??
Can I hide tooltips ("Click once to follow the hyperlink....") with VBA?
i need formula for drop down working hyperlinks, is it possible to do without vba???
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.
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!
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.
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.
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.
Its working great, Thanks Bill.
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.
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.
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
WOW! Thanks for explaining the code step by step.
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
Is it possible to build Macro for the Back function on each spreadsheet to have a button instead of a link?
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
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?
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 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
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 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;
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.
Absolutely fantastic.
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. :(
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, 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
Well done, excellent tutorial, thank you.
Sir when I try the addwatch target there is no + sign beside the shades logo.
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
Great trick Bill. Thanks
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
Very Cool!
Thanks for the quick response, i will give it a try and let you know how it goes!
Thanks again!
Amazing and very helpful!..thanks
Hi, what if the link is in the object of the worksheet, may we know the code to use? Thank you.
same issue here
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,
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 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 need help . can you the same type of function in google spread sheet.
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/
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.
Can We Use Worksheet 1's Activate Event To Hide All The Sheets After Following The Hyperlink? For Each... Next Loop
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
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
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.
this Already 1 year or more, but still work. thank you
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.
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,
Im having a problem, so from the hidden one (say number 2) that was linked to the main sheet(1), i also added another hyperlink to another hidden file(3). However, now even when i try moving forward from (2-->3) its closing 2 and sending me back to the main 1 while still opening 3 but not putting me straight on it i hope im not confusing you.
What im trying to say is after clicking the first hyperlink, and reaching tab (2), i want tab (2) to hide after Both going back to tab (1) or moving forward to tab (3)
Bill Jelen lovely thanks for ur help really appreciate it ill try it at work tomorrow and ill let u know how it goes
Hi again Bill, one more question, is it possible to do this, but from a drop down list hyperlinked to a hidden sheet?
I have the lists named in sheet 1, and the drop down list and the "go" button on page 2 and i want to go to page 3. Any ideas?
i created the named ranges on the "main" and created hyperlinks there to sheets 2,3,4,5,etc...then on "sheet 1" i created the drop down list using data validation, in the cell next to the drop down list i wrote the function hyperlink("[document name]main"!&A12,"Go") to activate the hyperlink, but when i hide the sheets and i try to go to the hyperlink it doesnt work and i tried adapting the code u wrote now it didnt work. Do i need to write any code in the "main" sheet, or do i only write the code u just suggested on the general pressed the go button of sheet 1 (which has the drop down list)?
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
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".
For all those stuck with an error at Worksheets (My sheet). visible = true. Try visible = HIDDEN. Worked for me 😁
Great! Thanks for the fast reply.
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...
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.
How to navigate the command button with multiple sheets hidden using excel?
Hi sir how can i fix it debug
Worksheets (MySheet).Visible = True
Please h3lp me
Me too 😢
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?
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.
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 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
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.
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
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!
Hi.. I still cant get it right. Dont v followhyperlink option. Can you help? Tq
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?
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
Don't worry, I worked it out - I missed the apostrophe from "".
Thanks - all good!
I want open a PDF file using hyperlink , and i want to specify the file name by a cell value , can you help me please ?
Thank you a lot , what if the cell i want the hyperlink in is dynamic , it keeps changing position using the "insert" function ?
but instead of "Click here for PDF" i want the cell's contents to be the text to hyperlink , i tried it and it sais some sort of formula error
=HYPERLINK("C:\Users\PC GAMER\Desktop\Mandy"&C6)
C6 as is the range where the name of the file is located in a cell .
the problem is when i click the cell i want it to be hyperlinked , it has a formula ."=Sheet1!G3"
i think i can make a variable on VBA , i just need on how to specify file using the string on the link .
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.
Hey Bill,I run this macro and continually get the runtime error `9`Subscript out of range, and when I debug it highlights this line:Worksheets(MySheet).Visible = Trueis there something wrong with this? The hyperlinks are not on the first sheet in the workbook, does that have something to do with it?Below is the whole code and I can`t see what it is that I am doing wrong.Please helpPrivate 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
Thanks Bill, what ended up being a solution for me was that I needed to eliminate any spaces between the words. Ended up just renaming them Income_Statement.
i try this code to the object instead of word but it doesnt work..can u help me how to use this code on object?
In case I did not mention it in the video, let me say it here: This only works from hyperlinks attached to cells, not hyperlinks attached to objects.
Hyperlinks from cells don't look as nice as hyperlinks from objects, but we all had to suck it up and switch to ugly hyperlinks if you want to have the VBA code to run.
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!😣
God Bless you Bill :)
So helpful!
Thanks..
This is amazing!
in windows 7 this formula support greatly.but windows 10 has not support this formula please help me how to solved it?
Do you see the Developer tab in the Ribbon? Click on Developer, Macro Security. Is the Windows 10 computer set to the top choice of "Disable all Macros Without Notification"? If it is, change it to the same choice that you are using in Windows 7.
In case you do not see the Developer tab, try this: (1) Right-Click the Ribbon and choose Customize Ribbon. (2) Look along the list box on the right side for Developer and check the box next to that tab. (3) Click OK.
Linkto=target.subaddress is not working i dont know why
i run but its error msg show "sub or function not define"
Copy your code and paste in reply so I can test it here.