Excel - Hyperlink to a Hidden Worksheet - Episode 1729

แชร์
ฝัง
  • เผยแพร่เมื่อ 30 ต.ค. 2024

ความคิดเห็น • 235

  • @shanalaurenc
    @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

  • @addrow8242
    @addrow8242 5 ปีที่แล้ว +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
      @addrow8242 5 ปีที่แล้ว

      A couple changes on these lines: WhereBang = InStr(1, LinkTo, "!")
      , MySheet = Left(LinkTo, WhereBang - 1)

    • @monojbanik3904
      @monojbanik3904 5 ปีที่แล้ว +1

      Thank you so much for identifying this with a solution:)

    • @ape15925
      @ape15925 5 ปีที่แล้ว

      Thanks for this!

    • @katarinaduris5619
      @katarinaduris5619 5 ปีที่แล้ว

      you are a star

  • @jasmeenmalhotra2225
    @jasmeenmalhotra2225 5 ปีที่แล้ว

    So helpful. Makes me realise just how much more there is to learn.

  • @sonai082055
    @sonai082055 4 ปีที่แล้ว +1

    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

  • @Nguroa
    @Nguroa 11 ปีที่แล้ว

    A great use of the "Watch" window, a very forgotten part of VBA.

  • @DarylSunga
    @DarylSunga 3 ปีที่แล้ว +1

    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.

  • @bmanning1989
    @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?

  • @dejabluek8297
    @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?

  • @hongkongqk
    @hongkongqk 10 ปีที่แล้ว

    The "Stop" command is very useful!! Thank you so much!!!

  • @LuisHernandez-cs5wf
    @LuisHernandez-cs5wf 9 ปีที่แล้ว +4

    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
      @wynnonaalinsunurin9017 5 ปีที่แล้ว

      I'm having the same prob. Can you teach us how? I'm using excel 2016.

  • @ronakgujarathi
    @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.

  • @courtneymatsumoto3400
    @courtneymatsumoto3400 7 ปีที่แล้ว

    This worked perfectly! I never really comment on videos, but I had to just say THANK YOU SO MUCH, this was amazing!

  • @TheChico
    @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

  • @phill_itofishfishingphill_8925
    @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.

  • @jamesmyers136
    @jamesmyers136 5 ปีที่แล้ว +1

    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?

  • @jamalhasanzakarneh9837
    @jamalhasanzakarneh9837 4 ปีที่แล้ว

    Hi Bill, when I tried to apply for the hyperlinking button, things do not work. What I can do please

  • @mwdasja1127
    @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
      @mwdasja1127 10 ปีที่แล้ว

      *****
      thanks for the suggestion Mr Jelen
      I will try this way..

    • @mwdasja1127
      @mwdasja1127 10 ปีที่แล้ว

      ***** Great sir, it is work properly..
      Thanks lot

    • @tegarmh1449
      @tegarmh1449 9 ปีที่แล้ว

      +Mawardi A. ASJA maaf pak bisa ajarkan saya saya menggunakan autoshape seperti yang bapak pakai ... Bisa saya diberi penjelasan untuk penyelesaianya? Terimakasih

  • @sukim711
    @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

  • @youssefmaha6
    @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

  • @excelisfun
    @excelisfun 11 ปีที่แล้ว

    Great video!

  • @pankajthakor6419
    @pankajthakor6419 4 ปีที่แล้ว

    I am getting error code "1004" Select method of Range class failed
    Please help

  • @GMCG2011
    @GMCG2011 10 ปีที่แล้ว

    Is it possible to make the sheets hide again after clicking back??

  • @conzept7470
    @conzept7470 4 ปีที่แล้ว

    Can I hide tooltips ("Click once to follow the hyperlink....") with VBA?

  • @afzaalahmed9565
    @afzaalahmed9565 5 ปีที่แล้ว

    i need formula for drop down working hyperlinks, is it possible to do without vba???

  • @esmatsaadaldeen1201
    @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

  • @jollyonlan
    @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!

  • @marcelogontinas8212
    @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.

  • @TheMak445
    @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
      @TheMak445 10 ปีที่แล้ว

      Thank you Bill. Very Kind of you.

  • @brandonw7858
    @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.

  • @fath1411
    @fath1411 9 ปีที่แล้ว

    Its working great, Thanks Bill.

  • @michaelconway5607
    @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.

  • @sheilaraines5312
    @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.

  • @excelcourseonline7213
    @excelcourseonline7213 9 ปีที่แล้ว +1

    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

  • @MySpreadsheetLab
    @MySpreadsheetLab 11 ปีที่แล้ว

    WOW! Thanks for explaining the code step by step.

  • @mattc6199
    @mattc6199 11 ปีที่แล้ว +2

    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

  • @RomanElham
    @RomanElham 8 ปีที่แล้ว

    Is it possible to build Macro for the Back function on each spreadsheet to have a button instead of a link?

  • @refticon
    @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

  • @betsyhardy6623
    @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?

  • @balajiraokj
    @balajiraokj 10 ปีที่แล้ว

    I could not get follow hyperlink command in the menu window of VB code editor. How to get it?

    • @balajiraokj
      @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?

  • @SrikumarRengaraj
    @SrikumarRengaraj 10 ปีที่แล้ว

    Hi Bill i am getting Runtime error 9 : Subscript out of range. How to resolve this issue?

    • @gauravsharma4787
      @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
    @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
      @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
      @husseinsalloukh1882 3 ปีที่แล้ว

      Same error: “Run time error ‘9’ subscript out of range”. The debug stops on “worksheets(MySheet).Visible = True”. PLEASE HELP

    • @MrXL
      @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.

  • @BrunoOlivera22
    @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
      @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;

  • @mehwishfiazmedia1942
    @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.

  • @shekharkumar8464
    @shekharkumar8464 7 ปีที่แล้ว

    Absolutely fantastic.

  • @maereegraceforonda7678
    @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. :(

  • @Rigcampboss
    @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.

  • @user-ig3yp7ft7m
    @user-ig3yp7ft7m 3 ปีที่แล้ว

    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

  • @cf0t0
    @cf0t0 9 ปีที่แล้ว

    Well done, excellent tutorial, thank you.

  • @PrincessEu-k1g
    @PrincessEu-k1g 7 หลายเดือนก่อน

    Sir when I try the addwatch target there is no + sign beside the shades logo.

  • @baileycoleman7478
    @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
      @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
      @baileycoleman7478 7 ปีที่แล้ว

      The Add Watch window doesn't pull anything up when I try to do that for Target in the first line

    • @baileycoleman7478
      @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

  • @krn14242
    @krn14242 11 ปีที่แล้ว

    Great trick Bill. Thanks

  • @dawnhudspeth3777
    @dawnhudspeth3777 11 ปีที่แล้ว +1

    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

  • @DUSTINISMS
    @DUSTINISMS 3 ปีที่แล้ว

    Very Cool!

  • @hachiroku2420
    @hachiroku2420 11 ปีที่แล้ว

    Thanks for the quick response, i will give it a try and let you know how it goes!
    Thanks again!

  • @RS_Rational
    @RS_Rational 8 ปีที่แล้ว

    Amazing and very helpful!..thanks

  • @ceciliaombao8575
    @ceciliaombao8575 4 ปีที่แล้ว

    Hi, what if the link is in the object of the worksheet, may we know the code to use? Thank you.

    • @TKTGalahad
      @TKTGalahad 4 ปีที่แล้ว

      same issue here

  • @thomas_allan
    @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
      @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

  • @itsfaes
    @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

  • @myominhtun4494
    @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
      @MrXL  2 ปีที่แล้ว +1

      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
      @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 ?

  • @ShreeRamaHanuman
    @ShreeRamaHanuman 6 ปีที่แล้ว

    I need help . can you the same type of function in google spread sheet.

  • @gediaz6959
    @gediaz6959 3 ปีที่แล้ว

    Hello.. how to hyperlink hidden sheets to textbox or pictures? Is it possible? Hope you read this

    • @MrXL
      @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/

  • @saracoppard2997
    @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
      @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
      @saracoppard2997 2 ปีที่แล้ว

      @@MrXL Thank you for your response.

  • @ashaydwivedi420
    @ashaydwivedi420 8 ปีที่แล้ว

    Can We Use Worksheet 1's Activate Event To Hide All The Sheets After Following The Hyperlink? For Each... Next Loop

  • @lillicob
    @lillicob 7 ปีที่แล้ว

    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
      @lillicob 7 ปีที่แล้ว

      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

  • @majdyehya7570
    @majdyehya7570 5 ปีที่แล้ว +2

    Worksheets(MySheet).Visible = True is always wrong. Any Help?

    • @sudoalex
      @sudoalex 5 ปีที่แล้ว

      You might have spaces in your sheet's name

    • @ahank1706
      @ahank1706 4 ปีที่แล้ว

      Same error. No spaces. Its exactly as it is in the video

  • @jamalhasanzakarneh9837
    @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.

  • @Ai_3ntertainment
    @Ai_3ntertainment 9 ปีที่แล้ว

    this Already 1 year or more, but still work. thank you

  • @paulleach9449
    @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
      @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
      @paulleach9449 6 ปีที่แล้ว

      Bill Jelen still no luck. It is a Mac problem (and unfortunately I need it work on a Mac).

    • @paulleach9449
      @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
      @paulleach9449 6 ปีที่แล้ว

      Thanks, bill - really appreciate the help. The compromise is just disabling the tab toolbar and leave out the coding.

  • @clinto1012
    @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!

  • @ramiqumri1926
    @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
      @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
      @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
      @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
      @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
    @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
      @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
      @butterfly4406 7 ปีที่แล้ว

      OMG!! It works perfect!! Thanks a lot Bill, you're a star :D

  • @HK-il3cu
    @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
      @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
      @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
      @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
      @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
      @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".

  • @kinagilkey1982
    @kinagilkey1982 5 ปีที่แล้ว

    For all those stuck with an error at Worksheets (My sheet). visible = true. Try visible = HIDDEN. Worked for me 😁

  • @trimegis2
    @trimegis2 11 ปีที่แล้ว

    Great! Thanks for the fast reply.

  • @soniastewart7593
    @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...

  • @himanshujames888
    @himanshujames888 9 ปีที่แล้ว +1

    Hey Bill, It was working, but when I opened my sheet again after closing, its not working

    • @MrXL
      @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
      @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
      @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
      @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.

  • @antoniovasayjr561
    @antoniovasayjr561 6 ปีที่แล้ว

    How to navigate the command button with multiple sheets hidden using excel?

  • @mr.virgin5747
    @mr.virgin5747 5 ปีที่แล้ว +1

    Hi sir how can i fix it debug
    Worksheets (MySheet).Visible = True
    Please h3lp me

  • @dawnhudspeth3777
    @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?

  • @johnwatkins39
    @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
      @johnwatkins39 7 ปีที่แล้ว

      Bill Jelen: thx Bill, i will give a try, if i have any issues i will get back with you.

  • @trimegis2
    @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!

  • @shmalan9698
    @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

  • @ThaoNguyen-gd7fi
    @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
      @winpins9539 6 ปีที่แล้ว

      Same here.

    • @addrow8242
      @addrow8242 5 ปีที่แล้ว

      See latest comment. Might be too late now but there is a solution.

  • @manishankar3054
    @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
      @vibeuponvibe 3 ปีที่แล้ว

      I got the same error. Did you manage to fix this? If so then please let me know how

  • @MiscaXL
    @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!

  • @Ocelxu
    @Ocelxu 11 ปีที่แล้ว

    Hi.. I still cant get it right. Dont v followhyperlink option. Can you help? Tq

  • @vinitmahale
    @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?

  • @leahcimonatnom
    @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
      @TKTGalahad 4 ปีที่แล้ว

      same problem

  • @richardsmith4573
    @richardsmith4573 11 ปีที่แล้ว

    Don't worry, I worked it out - I missed the apostrophe from "".
    Thanks - all good!

  • @JeremyFisher
    @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
      @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
      @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
      @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
      @JeremyFisher 7 ปีที่แล้ว

      the problem is when i click the cell i want it to be hyperlinked , it has a formula ."=Sheet1!G3"

    • @JeremyFisher
      @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 .

  • @majed
    @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.

  • @stephendumont8328
    @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
      @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.

  • @karlabe7961
    @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
      @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.

  • @sudoalex
    @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!😣

  • @jascoent3245
    @jascoent3245 7 ปีที่แล้ว

    God Bless you Bill :)

  • @mohinikaur966
    @mohinikaur966 8 ปีที่แล้ว

    So helpful!
    Thanks..

  • @aliceloftus935
    @aliceloftus935 9 ปีที่แล้ว

    This is amazing!

  • @ripondas5987
    @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
      @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.

  • @loairadwan212
    @loairadwan212 4 ปีที่แล้ว

    Linkto=target.subaddress is not working i dont know why

  • @Deepakraj249
    @Deepakraj249 6 ปีที่แล้ว

    i run but its error msg show "sub or function not define"

    • @MrXL
      @MrXL  6 ปีที่แล้ว

      Copy your code and paste in reply so I can test it here.