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
@shanalaurenc
11 жыл бұрын
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
@bmanning1989
8 жыл бұрын
In the drop down (General), worksheet is not available? How do I make this visible so I can begin writing my first usable macro?
@jamesmyers136
5 жыл бұрын
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?
@phill_itofishfishingphill_8925
11 жыл бұрын
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.
@dejabluek8297
4 жыл бұрын
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?
@lillicob
6 жыл бұрын
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?
@lillicob
6 жыл бұрын
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
@jasmeenmalhotra2225
5 жыл бұрын
So helpful. Makes me realise just how much more there is to learn.
@jamalhasanzakarneh9837
4 жыл бұрын
Hi Bill, when I tried to apply for the hyperlinking button, things do not work. What I can do please
@LuisHernandez-cs5wf
9 жыл бұрын
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
@wynnonaalinsunurin9017
5 жыл бұрын
I'm having the same prob. Can you teach us how? I'm using excel 2016.
@courtneymatsumoto3400
7 жыл бұрын
This worked perfectly! I never really comment on videos, but I had to just say THANK YOU SO MUCH, this was amazing!
@johnwatkins39
7 жыл бұрын
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.
@johnwatkins39
7 жыл бұрын
Bill Jelen: thx Bill, i will give a try, if i have any issues i will get back with you.
@sonai082055
4 жыл бұрын
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
@jamalhasanzakarneh9837
8 жыл бұрын
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.
@jamalhasanzakarneh9837
8 жыл бұрын
Thank you Bill. Yes you are right.
@ronakgujarathi
4 жыл бұрын
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.
@sukim711
2 жыл бұрын
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
@mattc6199
11 жыл бұрын
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
@mehwishfiazmedia1942
7 жыл бұрын
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.
@maereegraceforonda7678
10 жыл бұрын
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. :(
@esmatsaadaldeen1201
7 жыл бұрын
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
@esmatsaadaldeen1201
7 жыл бұрын
Thank you, I got it.
@TheChico
6 жыл бұрын
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
@TheMak445
10 жыл бұрын
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.
@TheMak445
10 жыл бұрын
Thank you Bill. Very Kind of you.
@SrikumarRengaraj
10 жыл бұрын
Hi Bill i am getting Runtime error 9 : Subscript out of range. How to resolve this issue?
@gauravsharma4787
7 жыл бұрын
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
@rhiannapagan8324
3 жыл бұрын
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?
@MrXL
3 жыл бұрын
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
3 жыл бұрын
Same error: “Run time error ‘9’ subscript out of range”. The debug stops on “worksheets(MySheet).Visible = True”. PLEASE HELP
@MrXL
3 жыл бұрын
@@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.
@jollyonlan
10 жыл бұрын
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!
@conzept7470
4 жыл бұрын
Can I hide tooltips ("Click once to follow the hyperlink....") with VBA?
@brandonw7858
9 жыл бұрын
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.
@Nguroa
11 жыл бұрын
A great use of the "Watch" window, a very forgotten part of VBA.
@hongkongqk
10 жыл бұрын
The "Stop" command is very useful!! Thank you so much!!!
@RomanElham
8 жыл бұрын
Is it possible to build Macro for the Back function on each spreadsheet to have a button instead of a link?
@addrow8242
5 жыл бұрын
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
@addrow8242
5 жыл бұрын
A couple changes on these lines: WhereBang = InStr(1, LinkTo, "!") , MySheet = Left(LinkTo, WhereBang - 1)
@monojbanik3904
5 жыл бұрын
Thank you so much for identifying this with a solution:)
@ape15925
5 жыл бұрын
Thanks for this!
@katarinaduris5619
5 жыл бұрын
you are a star
@ramiqumri1926
7 жыл бұрын
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.
@ramiqumri1926
7 жыл бұрын
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)
@ramiqumri1926
7 жыл бұрын
Bill Jelen lovely thanks for ur help really appreciate it ill try it at work tomorrow and ill let u know how it goes
@ramiqumri1926
7 жыл бұрын
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?
@ramiqumri1926
7 жыл бұрын
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)?
@butterfly4406
7 жыл бұрын
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..
@butterfly4406
7 жыл бұрын
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.
@butterfly4406
7 жыл бұрын
OMG!! It works perfect!! Thanks a lot Bill, you're a star :D
@DarylSunga
3 жыл бұрын
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.
@pankajthakor6419
4 жыл бұрын
I am getting error code "1004" Select method of Range class failed Please help
@thomas_allan
10 жыл бұрын
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
@thomas_allan
10 жыл бұрын
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
@GMCG2011
10 жыл бұрын
Is it possible to make the sheets hide again after clicking back??
@HK-il3cu
8 жыл бұрын
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?
@HK-il3cu
8 жыл бұрын
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.
@HK-il3cu
8 жыл бұрын
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.
@HK-il3cu
8 жыл бұрын
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?
@HK-il3cu
8 жыл бұрын
Nevermind. I think I got it to work. If I come across anything, I'll post. This is awesome! Thank You!
@HK-il3cu
8 жыл бұрын
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".
@saracoppard2997
2 жыл бұрын
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?
@MrXL
2 жыл бұрын
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.
@saracoppard2997
2 жыл бұрын
@@MrXL Thank you for your response.
@soniastewart7593
10 жыл бұрын
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...
@afzaalahmed9565
5 жыл бұрын
i need formula for drop down working hyperlinks, is it possible to do without vba???
@sheilaraines5312
9 жыл бұрын
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.
@BrunoOlivera22
5 жыл бұрын
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?
@BrunoOlivera22
5 жыл бұрын
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;
@stephendumont8328
7 жыл бұрын
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
@stephendumont8328
7 жыл бұрын
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.
@itsfaes
10 жыл бұрын
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
@Rigcampboss
10 жыл бұрын
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.
@betsyhardy6623
8 жыл бұрын
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?
@michaelconway5607
11 жыл бұрын
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?
@baileycoleman7478
7 жыл бұрын
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..
@baileycoleman7478
7 жыл бұрын
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
@baileycoleman7478
7 жыл бұрын
The Add Watch window doesn't pull anything up when I try to do that for Target in the first line
@baileycoleman7478
7 жыл бұрын
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
@vinitmahale
10 жыл бұрын
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?
@youssefmaha6
2 жыл бұрын
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
@marcelogontinas8212
6 жыл бұрын
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.
@paulleach9449
6 жыл бұрын
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!?
@paulleach9449
6 жыл бұрын
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
@paulleach9449
6 жыл бұрын
Bill Jelen still no luck. It is a Mac problem (and unfortunately I need it work on a Mac).
@paulleach9449
6 жыл бұрын
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 ☹️
@paulleach9449
6 жыл бұрын
Thanks, bill - really appreciate the help. The compromise is just disabling the tab toolbar and leave out the coding.
@ashaydwivedi420
8 жыл бұрын
Can We Use Worksheet 1's Activate Event To Hide All The Sheets After Following The Hyperlink? For Each... Next Loop
@hachiroku2420
11 жыл бұрын
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!
@leahcimonatnom
8 жыл бұрын
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
@TKTGalahad
4 жыл бұрын
same problem
@clinto1012
6 жыл бұрын
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!
@ceciliaombao8575
4 жыл бұрын
Hi, what if the link is in the object of the worksheet, may we know the code to use? Thank you.
@TKTGalahad
4 жыл бұрын
same issue here
@balajiraokj
10 жыл бұрын
I could not get follow hyperlink command in the menu window of VB code editor. How to get it?
@balajiraokj
10 жыл бұрын
***** 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?
@mwdasja1127
10 жыл бұрын
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
@mwdasja1127
10 жыл бұрын
***** thanks for the suggestion Mr Jelen I will try this way..
@mwdasja1127
10 жыл бұрын
***** Great sir, it is work properly.. Thanks lot
@tegarmh1449
9 жыл бұрын
+Mawardi A. ASJA maaf pak bisa ajarkan saya saya menggunakan autoshape seperti yang bapak pakai ... Bisa saya diberi penjelasan untuk penyelesaianya? Terimakasih
@myominhtun4494
2 жыл бұрын
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.
@MrXL
2 жыл бұрын
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).
@myominhtun4494
2 жыл бұрын
@@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 ?
@PrincessEu-k1g
7 ай бұрын
Sir when I try the addwatch target there is no + sign beside the shades logo.
@trimegis2
11 жыл бұрын
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!
@majed
11 жыл бұрын
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.
@majdyehya7570
5 жыл бұрын
Worksheets(MySheet).Visible = True is always wrong. Any Help?
@sudoalex
5 жыл бұрын
You might have spaces in your sheet's name
@ahank1706
4 жыл бұрын
Same error. No spaces. Its exactly as it is in the video
@sudoalex
5 жыл бұрын
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!😣
@shmalan9698
2 жыл бұрын
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
@dawnhudspeth3777
11 жыл бұрын
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?
@gediaz6959
3 жыл бұрын
Hello.. how to hyperlink hidden sheets to textbox or pictures? Is it possible? Hope you read this
@MrXL
3 жыл бұрын
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/
@ThaoNguyen-gd7fi
6 жыл бұрын
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
@winpins9539
6 жыл бұрын
Same here.
@addrow8242
5 жыл бұрын
See latest comment. Might be too late now but there is a solution.
@himanshujames888
9 жыл бұрын
Hey Bill, It was working, but when I opened my sheet again after closing, its not working
@MrXL
6 жыл бұрын
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.
@TheChico
6 жыл бұрын
@@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
@nikiss8
6 жыл бұрын
@@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
@MrXL
6 жыл бұрын
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.
@mr.virgin5747
5 жыл бұрын
Hi sir how can i fix it debug Worksheets (MySheet).Visible = True Please h3lp me
@kinagilkey1982
5 жыл бұрын
Me too 😢
@antoniovasayjr561
6 жыл бұрын
How to navigate the command button with multiple sheets hidden using excel?
@manishankar3054
4 жыл бұрын
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
@vibeuponvibe
3 жыл бұрын
I got the same error. Did you manage to fix this? If so then please let me know how
@ShreeRamaHanuman
6 жыл бұрын
I need help . can you the same type of function in google spread sheet.
@michaelconway5607
11 жыл бұрын
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.
@ripondas5987
6 жыл бұрын
in windows 7 this formula support greatly.but windows 10 has not support this formula please help me how to solved it?
@MrXL
6 жыл бұрын
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.
@himanshuvats2207
10 жыл бұрын
Hey Bill Thanks for the great video, i am new to VB i tried the code mentioned in your video.... I am getting Run Time error '9' can you please elaborate why i am getting this error and how can i debug..
@himanshuvats2207
10 жыл бұрын
***** Hey Bill, i found the mistake. The problem is with space and extra character on my sheet name I tried doing apostrophes (MySheet = Application.WorksheetFunction.Substitute(MySheet, "", "")) but its not working, can you please check the code if its working with sheet names having space and special character.
@JeremyFisher
7 жыл бұрын
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 ?
@JeremyFisher
7 жыл бұрын
Thank you a lot , what if the cell i want the hyperlink in is dynamic , it keeps changing position using the "insert" function ?
@JeremyFisher
7 жыл бұрын
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
@JeremyFisher
7 жыл бұрын
=HYPERLINK("C:\Users\PC GAMER\Desktop\Mandy"&C6) C6 as is the range where the name of the file is located in a cell .
@JeremyFisher
7 жыл бұрын
the problem is when i click the cell i want it to be hyperlinked , it has a formula ."=Sheet1!G3"
@JeremyFisher
7 жыл бұрын
i think i can make a variable on VBA , i just need on how to specify file using the string on the link .
@JENNY67676
11 жыл бұрын
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
@karlabe7961
6 жыл бұрын
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?
@MrXL
6 жыл бұрын
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.
@fath1411
9 жыл бұрын
Its working great, Thanks Bill.
@myrtoananida9659
6 жыл бұрын
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.
@MrXL
6 жыл бұрын
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
@Ocelxu
11 жыл бұрын
Hi.. I still cant get it right. Dont v followhyperlink option. Can you help? Tq
@excelcourseonline7213
9 жыл бұрын
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
@refticon
9 жыл бұрын
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
@MySpreadsheetLab
11 жыл бұрын
WOW! Thanks for explaining the code step by step.
@excelisfun
11 жыл бұрын
Great video!
@richardsmith4573
11 жыл бұрын
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
@himanshujames888
9 жыл бұрын
Hey Bill, Its working great. But If I send my same excel sheet to someone else, will it work there also.
@himanshujames888
9 жыл бұрын
Himanshu James please help urgent
@krn14242
11 жыл бұрын
Great trick Bill. Thanks
@shekharkumar8464
7 жыл бұрын
Absolutely fantastic.
@kelliereviews5341
6 жыл бұрын
I wonder if there is a way, that i can use that as a macro? Example my client data base I have in a macro, and I can pull it up from the main screen (dashboard). But once I hide it, im not able to do that. I was wondering if there was a way that I could use a hyperlink in the macro to open the hidden page. and then close it?
@MrXL
6 жыл бұрын
Yes. Anyone with some VBA skills can adapt the macro for you. Check out the ExcelRescue people here: www.mrexcel.com/consulting-services/
@kelliereviews5341
6 жыл бұрын
thanks, but I have more time than money...... plus there are more videos on youtube and other platforms that can provide help. Thanks anyway
@NMMman
11 жыл бұрын
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!
@Ai_3ntertainment
9 жыл бұрын
this Already 1 year or more, but still work. thank you
@tinatan34
4 жыл бұрын
Thanks Bill for the vedio Worksheets(MySheet).Visible = True Is always debug I tried to delete space of the sheet name no lucky Please help me! Thank you
@zakirshaikh7166
6 жыл бұрын
Hi Bill, great video, I have a problem, I am getting an error, "Run-time error '9' subscript out of range". below is the query. 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 Please help.
@MrXL
6 жыл бұрын
Can you try something for me? You just clicked on a hyperlink. That hyperlink is supposed to go a worksheet. I need you to go to that worksheet. Right-click on the sheet tab and choose Rename. Change the name of the sheet to remove all spaces, numbers, punctuation. Use only letters. Try the hyperlink again and let me know if that works.
@zakirshaikh7166
6 жыл бұрын
Hi Bill, Its working now, Thank you so much!
@zakirshaikh7166
6 жыл бұрын
Hey Bill, What if i want give multiple hyperlinks in sheet2 & sheet3, For example, from in sheet 2 i want to go to sheet3 instead of menu and i want to have menu option as well in the same sheet.
@MrXL
6 жыл бұрын
It is possible to do, but it is significantly more complex code. I am looking at the code as it exists at 4:09 mark in the video. The LinkTo variable is holding the address specified in the link to address. If you have only a few links, you could do something like this: Select Case LinkTo Case "Menu!A10" Worksheets("Menu").Visible = True Worksheets("Menu").Select Case "Sheet3!A1" Worksheets("Sheet3").Visible = True Worksheets("Sheet3").Select Case "Sheet3!A5" Worksheets("Sheet3").Visible = True Worksheets("Sheet3").Select Range("A5").Select ' Keep adding more blocks of code as necessary End Select Or, if you have hundreds of links, you could use the WhereBang variable to break the link in to two pieces SheetName = Left(LinkTo,WhereBang-1) RangeName = Mid(LinkTo,WhereBang+1) But then if you use sheet names like "Income Statement" instead of "IncomeStatement", you will have to strip off the apostrophes.
@zakirshaikh7166
6 жыл бұрын
OK bill, Can i define a range in the same sheet. like i want the hyperlink to take me to the specific cell in the specific sheet.
@MiscaXL
11 жыл бұрын
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!
@jascoent3245
7 жыл бұрын
God Bless you Bill :)
@rizleymihular4194
8 жыл бұрын
I USED THE SAME VBA CODES AS PER EPISODE 1729 , BUT THE MENU TO HIDDEN WORKSHEET DOES NOT WORK
@rizleymihular4194
8 жыл бұрын
+Bill Jelen I saved the WB as mentioned by you , but still the menu to hidden worksheet does not work
@rizleymihular4194
8 жыл бұрын
+Bill Jelen Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) LinkTo = Target.SubAddress In the Menu worksheet , I have entered- 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 In the hidden worksheets , I have entered- Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Worksheets("Menu").Select Target.Parent.Worksheet.Visible = False End Sub
@rizleymihular4194
8 жыл бұрын
Bil Jalen, In the menu , I have entered- 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 In the hidden worksheets , I have entered- Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Worksheets("Menu").Select Target.Parent.Worksheet.Visible = False End Sub
@rizleymihular4194
8 жыл бұрын
+Bill Jelen I added the 6th line addition as requested. Also in the Macro security have set "disable all Macros with Notification " . I also entered the VBA codes through Developer tab and view codes in the order mentioned by the video. However still unable to go to hidden worksheet by clicking the relevant tab in the menu.
@rizleymihular4194
8 жыл бұрын
+Bill Jelen Thank you Bill Jelen. Now it is working . Appreciate very much your early response to my questions.
Пікірлер: 235