Thank you for Explaining the VBA code.Bcz every youtube channel just copy and paste the code.I want to understand the code that how its work. A big Thanks👍
Hi, this is a great video thank you. However I keep getting an error message that says subscript out of range. I believe it has something to do with the xlsheetvisible option. Could you advise please?
Great work! I'be been scouring several excel communities for this particular project, but because VBA is completely strange to me, I was not able to come up with success story. My first impression on instructional videos is that it is a waste of time, and 95% of the time it failed to provide me solutions. This one is such a particular instance wherein I gave up chasing for answers from forumers and switch to IVs. Such a luck I clicked your video, and Tadaah! Problem solved. Your amazing! I was able to follow the instructions, and the bubble problem in my head has just been pin-bursted with your clear and concise instructions. Continue your wonderful work and a massive thank you
Thank you so much for this awesome video. That was exactly what I was looking for! You are also talking so clearly and that means non-natives can also understand everything perfectly. (Like me 😝) Edit: Can somebody also explain how to do it if they were not cell values but TextBox values?
For info. If you have a sheet tab name with a space in between, e.g. "Daily Totals" you will need to replace the space between the words with an underscore '_'. so "DAILY TOTALS" -------> "DAILY_TOTALS", this should help with some instances of the 'Subscript out of Range' error
Hi ComputerGaga, I'm getting an issue with this giving me a runtime error 9. I've got quite a few worksheets over 60 and I'm trying to use this in combination with your "Drop Down List of Hyperlinks - Excel Hyperlinks Tip" video. i'm wondering what I can do to fix this as i suspect it has something to do with the number of worksheets.
Hi Computergaga, thank you for the walk-through for us vba bagginers. I applied these codes on multiple sheets and they worked perfectly, however, my workbook got slower ever since I added the codes, I copy/pasted them on multiple sheets (first code for the sheets that contain the hyperlinks and the second code for the previously hidden sheets to re-hide them). I was wondering if there is a way to apply the first code on all the sheets that contain the hyperlinks to the hidden sheets without copy/pasting the code on each sheet? Same for the second code.
Very good tutorial! Out of all the videos I’ve watched yours was the only one the actually worked. However, how would I do this with a shape? I want to link shapes with text to a hidden sheet, and then hide that sheet after returning to the menu.
@@TheGingerdevil1 No, I have not figured out how to do this yet. Instead of labeling my tabs with their actual name, I’ve just numbered them so that they all fit along the bottom without having to scroll left-or-right and linked my shapes to their appropriate tabs. You can still see them but it looks cleaner than it did before.
@@Computergaga I'v found your tutorial quite simple and clear, however when l follow the exact steps you did, I keep getting a message of error. I dunno if I'm missing something (which i'm sure i am) or what. so my question is: do I have to set up my excel file to certain settings before carrying out the work you did?
Hi, Thanks for sharing your knowledge with us. This code helped me a lot. But now I am facing a little issue in this that If I am using shape like a button linked to another page, this code is not working. Can you please help me with that if possible? Many Thanks, Raman
It worked for one of my projects but what if I don't want to rename the sheets the same as the values on the cells? Can I really still pull out the sheets that I need if they have a different names?
This works perfectly however if i also hyperlink to a file on a network drive i get an error which if debugged is because its not on the hidden sheets, can i have multiple rules?
Hi thank you for this!!! Big help, also can you help me?? What if i also have a hyperlink in 'Apples' that will go to another sheet (eg. 'Green Apple) but how can i hide/unhide Green Apple sheet like what i did to other sheets? What is the code on vba?? Hope you can help me. Thanks
Thanks for the tutorial. I followed along but am still getting an error for tabs that aren't named the same as the cell. My text does not match the name on the tab as it does not allow the same amount of characters that I can type into a cell. For example, for Academic Support Compensation Procedures I can only put at most "Academic Support Compensation P" as the tab name. If you change the text on the Main tab from "Apples" to "Apples Form", and the Apples tab remains named "Apples", will it still link you to the Apples tab? I see it worked for you, but you didn't change the text in the cell or the name on the tab.
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?
Try double clicking on the Sheet tab. At the top of the window on the title bar it will say if you are in a Module or a Sheet code window. You need to be in that sheet to see the drop downs.
Hi.. i have tried the same way but code is not working for me.. 1st i have saved file as macro enabled and then select my main sheet and open vba from developer tab.. write this code but didn’t work.. can you please tell me where am I wrong???
When I tried to use the when where the names do not match, it showed an error. Clicked to "debug" and it highlighted in yellow the "Sheets(shtName).Visible = xlSheetVisible"
It did the same for me but my sheets have the same names. Did you find out what the problem was? I was able to fix mine by changing/shortening the names of my sheets.
It works for Deactivate but I still hve issues with the FollowHyperlinks. it won't still open if I hide it. I am not sure what the issue is. But I have noticed my sheet is not in order. It shows Sheet1 then followed by Sheet10-19 the Sheet 2-9. May be this is the issue because it should be sheets 1-19 but I couldn't or don't know how to sort if from sheet 1-19
Hi Can someone help me here. When i am running the command I am getting run time error 9 and the error is highlighted here. Sheets(shtname).Visible = xlSheetVisible
I have the same issue. But instead of multiple excel sheets, if it's just a workbook of 2 to 5 sheets this code works perfectly. Can u please design a code that will work on multiple sheets (more than 10 sheets to link in a workbook of 15 sheets)
Sachin Satheesh i am currently facing the same issue, tried to apply this in a small workbook and it worked perfectly, but when I tried to do it to one with more than 50 it shows the error and highlights "sheets(shtname).visible=xlsheetvisible"
Good day Sir. My name is Donald from South Africa I have been following your Excel teachings thank you so much for the knowledge you are sharing with us. I have a bit of a problem with the spreadsheet I am working on currently The hyperlinks wouldn't work to the hidden sheet I don't know if maybe the problem is that the name on the link isn't the same as the sheet name Can I please send you my spreadsheet and help me with it
Have you tried the technique in the video? The display text of the hyperlink is not a problem, but the link path must be accurate. You cannot link to a hidden sheet in Excel. So, this video shows a workaround.
When I am sharing my sheet on a shared drive in office using hyperlink function (not onedrive or 365), my colleague is unable to open the links done. Any help???
I'm not sure. I would check the path of the links and their destination. Whether the colleague has access to the destination, and if the path is different for them.
When using a hyperlink formula, how can I link to hidden tab? I use the “#’” along with Vlookup and “‘!A1” to create hyperlink which works on unhidden tab, but once hidden I cannot get it to work. Any ideas?
I appreciate the fast response! My issue is that in using a hyperlink formula with vlookup I cannot get the VBA to unhide the tab. The VBA works with a hyperlink created manually but not with my formula. Thanks!
One further piece of info that may be important, the displayed text in the cell without the formula appears to match the hidden tab name, but I cannot get it to unhide with either method. I assume it may need a variation in the VBA for hyperlinks created by a formula
Hi this is awesome code how ever can help me, i am getting and error code run time error '9': subscript out of range, dont why i am getting this error, can you help me. thanks
WHEN I HIT THE HYPERLINK ON THE MAIN, IT DOESNT FUNCTION, THE HYPERLINK WONT LINK IT TO THE HIDDEN SHEET. I KEPT ON SMASHING THEIRS NO SHEET POPPED UP. I TRIED THIS METHOD BUT IT WONT WORK, BUT THE HIDING INACTIVE SHEET WAS REALLY A GREAT HELP.
You just save me a thousand years of thinking how to hide and link these sheets. Thanks a lot Sir! ❤️
Dear sir , I dont know anything about programming . BUT I DID IT. Thank you very very much Sir 🙏🙏 ( Sorry for poor English)
Thank you for Explaining the VBA code.Bcz every youtube channel just copy and paste the code.I want to understand the code that how its work. A big Thanks👍
Hi, this is a great video thank you. However I keep getting an error message that says subscript out of range. I believe it has something to do with the xlsheetvisible option. Could you advise please?
Great work! I'be been scouring several excel communities for this particular project, but because VBA is completely strange to me, I was not able to come up with success story. My first impression on instructional videos is that it is a waste of time, and 95% of the time it failed to provide me solutions. This one is such a particular instance wherein I gave up chasing for answers from forumers and switch to IVs. Such a luck I clicked your video, and Tadaah! Problem solved. Your amazing! I was able to follow the instructions, and the bubble problem in my head has just been pin-bursted with your clear and concise instructions. Continue your wonderful work and a massive thank you
You're very welcome. Thank you.
Thank you so much for this awesome video. That was exactly what I was looking for! You are also talking so clearly and that means non-natives can also understand everything perfectly. (Like me 😝)
Edit: Can somebody also explain how to do it if they were not cell values but TextBox values?
Thank you so much for your Nice Explanation of VBA Macro. I want to more video for Microsoft Excel VBA Macro.👌👌👌👌
Hi Computergaga this tutorial is perfectly fit and works for what I wanted to happen to my inventory excel.. Your the best.
Great to hear it. Thank you.
For info.
If you have a sheet tab name with a space in between, e.g. "Daily Totals" you will need to replace the space between the words with an underscore '_'. so "DAILY TOTALS" -------> "DAILY_TOTALS", this should help with some instances of the 'Subscript out of Range' error
Hi ComputerGaga, I'm getting an issue with this giving me a runtime error 9. I've got quite a few worksheets over 60 and I'm trying to use this in combination with your "Drop Down List of Hyperlinks - Excel Hyperlinks Tip" video. i'm wondering what I can do to fix this as i suspect it has something to do with the number of worksheets.
Thanks a lot. You made my job easy. wishing you most and more.👍
Glad it helped. Thank you.
Thank you so much , No words to say . You solved a big as I needed. Thanks again sir
You are most welcome
Thank a lot so much your tutorial for VBA beginner
Excellent tutorial. Thank you!
You're welcome Keisha.
That was really good - so easy to follow! Keep up the grat work!
Thank you Alison.
Excellent tutorial - so practical and relevant. Much appreciated!!
You're welcome. Thank you.
I can't thank you enough for this - an excellent tutorial and quite easy for a VBA Beginner
Great to hear. Thank you.
Hey, I just set this up and it works great, I was wondering if there was a way to search the hidden files from main sheet. Thanks for any help
Hi Computergaga, thank you for the walk-through for us vba bagginers. I applied these codes on multiple sheets and they worked perfectly, however, my workbook got slower ever since I added the codes, I copy/pasted them on multiple sheets (first code for the sheets that contain the hyperlinks and the second code for the previously hidden sheets to re-hide them). I was wondering if there is a way to apply the first code on all the sheets that contain the hyperlinks to the hidden sheets without copy/pasting the code on each sheet? Same for the second code.
Thanks a lot for this information you are really fantastic to explain and share information in an easy way!
So nice of you. Thank you.
Very good tutorial! Out of all the videos I’ve watched yours was the only one the actually worked.
However, how would I do this with a shape? I want to link shapes with text to a hidden sheet, and then hide that sheet after returning to the menu.
I want to do this aswell, have you found out how yet?
I want to know this to.
@@TheGingerdevil1 No, I have not figured out how to do this yet. Instead of labeling my tabs with their actual name, I’ve just numbered them so that they all fit along the bottom without having to scroll left-or-right and linked my shapes to their appropriate tabs. You can still see them but it looks cleaner than it did before.
Thank you very much. Great video, subject and explanation!
Thank you, Jan 😊
Hi This is great. Been looking for this everywhere, great stuff. Thank you.
You're welcome. Thank you 👍
Wow very nice video.
This is what im looking for..
You earned a subscription for that...
Thanks
Thanks for the sub!
And the comments 👍
while trying the link in cells, its working, but when i hyperlinked with shape(like square) its not working., suggest any comments
The first code didn’t work for me. The second one does but it doesn’t take me to the specific cell in the worksheet. Please help.
it doesn't work for me! please help me. when I click on hyper link this error appears:
Run-time error '9':
subscript out of range
HELP?
thank you so much , no words to say .
Most welcome Arun 😊
Great video, simple to follow and have been able to easily replicate, I will be sure to check out your other videos! Thank you!
Excellent! Thank you Glen.
Amazing work, outstanding explanation and professional maneuvers
Thank you 😊
@@Computergaga I'v found your tutorial quite simple and clear, however when l follow the exact steps you did, I keep getting a message of error. I dunno if I'm missing something (which i'm sure i am) or what. so my question is: do I have to set up my excel file to certain settings before carrying out the work you did?
No specific settings required. Apart from macros enabled.
@@Computergaga Whenever you type "shtName" do I have to type the exact name given to the sheet l'm working on?
Thanks much for this video. Also i need to know how can i hide mutlple column, ex A,C,E,G,J by using VBA
This is great!
Thank you 😊
awesome !!!!
Thanks!!
Hi,
Thanks for sharing your knowledge with us. This code helped me a lot.
But now I am facing a little issue in this that If I am using shape like a button linked to another page, this code is not working.
Can you please help me with that if possible?
Many Thanks,
Raman
It worked for one of my projects but what if I don't want to rename the sheets the same as the values on the cells? Can I really still pull out the sheets that I need if they have a different names?
You are awesome!
Thank you, Jenofanes.
This works perfectly however if i also hyperlink to a file on a network drive i get an error which if debugged is because its not on the hidden sheets, can i have multiple rules?
Nice work bro
Thank you 😊
Great stuff..
Thanks, Gabriel.
Very useful video thank you so much
Can you please convert this demo for google sheets
Thank you, Pankaj. Sorry, I don't use Google Sheets.
I keep getting runtime error 9 - subscript out of range when trying both methods, not sure what im doing wrong here.
I don't have hyperlinks, but macro's to open the sheets. When i hide the sheets then the sheet will not open after running the macro. Can you help me.
Hi thank you for this!!! Big help, also can you help me??
What if i also have a hyperlink in 'Apples' that will go to another sheet (eg. 'Green Apple) but how can i hide/unhide Green Apple sheet like what i did to other sheets? What is the code on vba?? Hope you can help me. Thanks
Thanks for the tutorial. I followed along but am still getting an error for tabs that aren't named the same as the cell. My text does not match the name on the tab as it does not allow the same amount of characters that I can type into a cell. For example, for Academic Support Compensation Procedures I can only put at most "Academic Support Compensation P" as the tab name.
If you change the text on the Main tab from "Apples" to "Apples Form", and the Apples tab remains named "Apples", will it still link you to the Apples tab? I see it worked for you, but you didn't change the text in the cell or the name on the tab.
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?
Try double clicking on the Sheet tab. At the top of the window on the title bar it will say if you are in a Module or a Sheet code window. You need to be in that sheet to see the drop downs.
Hi.. i have tried the same way but code is not working for me..
1st i have saved file as macro enabled and then select my main sheet and open vba from developer tab.. write this code but didn’t work.. can you please tell me where am I wrong???
When I tried to use the when where the names do not match, it showed an error. Clicked to "debug" and it highlighted in yellow the "Sheets(shtName).Visible = xlSheetVisible"
It did the same for me but my sheets have the same names. Did you find out what the problem was? I was able to fix mine by changing/shortening the names of my sheets.
Thanks for this tutorial, what if i have a shape link to a sheet and i want to hide that sheet . Any help . Thanks
I think it works the same way. A link won't work to a hidden sheet, so VBA is used to unhide the sheet.
It works for Deactivate but I still hve issues with the FollowHyperlinks. it won't still open if I hide it. I am not sure what the issue is. But I have noticed my sheet is not in order. It shows Sheet1 then followed by Sheet10-19 the Sheet 2-9. May be this is the issue because it should be sheets 1-19 but I couldn't or don't know how to sort if from sheet 1-19
SAME DILLEMMA
Hello question, why is not working when I shared it with my co-workers? It's only working when i click the links
Is it possible to make the hyperlink ask for a password before it opens the sheet?
Thank you, great video.
I guess we could create this kind of effect with a userform and test their input.
Hi Can someone help me here. When i am running the command I am getting run time error 9 and the error is highlighted here. Sheets(shtname).Visible = xlSheetVisible
Check the sheet name assigned to the shtname variable Raunak. That error indicates that he does not know this sheet.
@@Computergaga I am getting this exact same error and cannot for the life of me work out how to make this work
I have multiple pages running and when I keep pressing my links even, I end up getting a runtime error 9 out of range notification.
This type of error normally indicates that it cannot see a sheet by that name.
I have the same issue. But instead of multiple excel sheets, if it's just a workbook of 2 to 5 sheets this code works perfectly. Can u please design a code that will work on multiple sheets (more than 10 sheets to link in a workbook of 15 sheets)
Sachin Satheesh i am currently facing the same issue, tried to apply this in a small workbook and it worked perfectly, but when I tried to do it to one with more than 50 it shows the error and highlights "sheets(shtname).visible=xlsheetvisible"
Same here, thank you.
You're very welcome Marek.
Why if i save it, and open it again, the formula has been lost? Any suggest?
Nice easy to follow only problem was my excel didn't have the subaddress function.
Thank you.
I get the error that "reference is not valid". What can I do?
this code doesnt work when you have done a hyperlink on a shape. Can you show code how to use it when the shape has a hyperlink?
Good day Sir.
My name is Donald from South Africa
I have been following your Excel teachings thank you so much for the knowledge you are sharing with us.
I have a bit of a problem with the spreadsheet I am working on currently
The hyperlinks wouldn't work to the hidden sheet
I don't know if maybe the problem is that the name on the link isn't the same as the sheet name
Can I please send you my spreadsheet and help me with it
Have you tried the technique in the video? The display text of the hyperlink is not a problem, but the link path must be accurate.
You cannot link to a hidden sheet in Excel. So, this video shows a workaround.
I did try but still is not working I don't know what might be the reason
HIe, when i hide my sheets and press the object for my hyperlinked sheet
its not loading, any help?
i had created hyperlinks on buttons to try making it a bit presentable but the code does not apply on links created on buttons
Is there a way to do this with out VBA?
Not that I'm aware of.
When I am sharing my sheet on a shared drive in office using hyperlink function (not onedrive or 365), my colleague is unable to open the links done. Any help???
I'm not sure. I would check the path of the links and their destination. Whether the colleague has access to the destination, and if the path is different for them.
Desperate for help. I have Sheets(shtName).Visible = xlSheetVisible highlighted and cannot fix it.
Ensure the step before it is correct Sam. Maybe it doesn't recognise the sheet. Do you get a error message?
why doenst work with me
When using a hyperlink formula, how can I link to hidden tab? I use the “#’” along with Vlookup and “‘!A1” to create hyperlink which works on unhidden tab, but once hidden I cannot get it to work. Any ideas?
The worksheet needs to be unhidden, so I used VBA in this tutorial.
I appreciate the fast response! My issue is that in using a hyperlink formula with vlookup I cannot get the VBA to unhide the tab. The VBA works with a hyperlink created manually but not with my formula. Thanks!
One further piece of info that may be important, the displayed text in the cell without the formula appears to match the hidden tab name, but I cannot get it to unhide with either method. I assume it may need a variation in the VBA for hyperlinks created by a formula
i wrote a perfectly identical code and it isnt working for me...
There will be a reason Anna, but hard to tell without seeing it all. Are you receiving an error?
My excel do not have any developer in menu .how to make it appear!
Click File > Options > Customise the Ribbon and check the Developer box on the right.
Thanks for the tutorial but can some one understand this code without basic knowledge of VBA?
It would be difficult. Some knowledge always helps.
Thanks for the comment 👍
Hi this is awesome code how ever can help me, i am getting and error code run time error '9':
subscript out of range, dont why i am getting this error, can you help me. thanks
Thank you Hugo. This would normally indicate that Excel doesn't recognise your sheet name. Check the sheet name used for inaccuracies.
@@Computergaga Hi am facing the same error, but where the sheet name to be replaced?
Thanks in advance!!!!
Make sure it says option explicit at the top. I was having this issue because option explicit was missing, so I typed it in and it worked
I WONDER WHY THIS DOESNT WORK WITH ME, AFTER WRITING THE VBA, THE HYPERLINK WONT FUNCTION. PLS HELP
WHEN I HIT THE HYPERLINK ON THE MAIN, IT DOESNT FUNCTION, THE HYPERLINK WONT LINK IT TO THE HIDDEN SHEET. I KEPT ON SMASHING THEIRS NO SHEET POPPED UP. I TRIED THIS METHOD BUT IT WONT WORK, BUT THE HIDING INACTIVE SHEET WAS REALLY A GREAT HELP.
WHEN THE SHEET IS NOT HIDDEN, IT WAS FINE, BUT WHEN I TRIED TO HIDE THE SHEET, THE CODE WONT WORK PLS PLS HELP
I’m gettin an out of range error. Guurr
Check the name of the sheet being used. Excel cannot find it.
@@Computergaga right on buddy. I had a space after the name. Appreciate the help my friend .
How To Hyperlink to a Hidden "Drop-down list" ? doesn't use VBA ,Thank You
Sir I need your email