1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How To Hyperlink To Cells In Hidden Worksheet

Discussion in 'Excel Dashboards' started by Lim Aina, Sep 10, 2014.

  1. Lim Aina

    Lim Aina New Member

    Messages:
    9
    Hi guys,

    I have a simple dashboard with four worksheet tabs. The first tab is the dashboard with many different charts that I would like to link to many different tables in two hidden sheets. I would like to have from the Dashboard, all the individual project doughnut charts linked to its corresponding tables in Planned Project List worksheet and all the individual initiative doughnut charts linked to its corresponding Planned Initiative List worksheet.

    As a simple dashboard, once completed I would like to hide all tabs except for the Dashboard and have the hyperlinks to the Planned Project List worksheet and Planned Initiative worksheet still working.

    I have Googled and tried several different macros found in forums but it still does not seem to work. I could be doing things wrongly as this is my first Excel attempting any macros, would appreciate any advise, thanks!

    Attached Files:

  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,591
    Hi Lim ,

    Again , I am not able to understand your requirement.

    See this file ; probably you can apply the same technique in your file.

    Narayan

    Attached Files:

    Sameer.k21, Thomas Kuriakose and GFC like this.
  3. Lim Aina

    Lim Aina New Member

    Messages:
    9
    Hi Narayan,

    Thank you this is exactly how I wanted it to work!!

    How did you do this? The normal way to hyperlink does no enables a hidden sheet to pop out and hides back again like what your sheet does. Is there a special macro for it? Where do I find it? If you could list the steps you went through for the above perhaps I can try it on mine.

    Sorry for the many questions, absolutely new to macros in Excel. Appreciate it! Thank you!
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,591
    Hi Lim ,

    There are 2 macros in the ThisWorkbook section :
    Code (vb):

    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
                If ((Sh.CodeName = "Sheet2") Or (Sh.CodeName = "Sheet3")) Then
                  Sh.Visible = xlSheetVeryHidden
                End If
    End Sub

    Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
                Sheetname = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)
                Sheets(Sheetname).Visible = True
                Application.EnableEvents = False
                Target.Follow
                Application.EnableEvents = True
    End Sub
     
    As you can see , the first procedure is for hiding the sheet that is deactivated ; the second is for unhiding and then acting on the hyperlink when you click on it.

    Narayan
  5. Shazz

    Shazz Member

    Messages:
    125
    Can anyone help me at all, I have tried the macro mentioned above and changed the names of the sheets in the code to match mine and it is not working when hidden.

    Any ideas??

    Shazz
    x
  6. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,591
    Hi ,

    Probably if you can upload your file , we can see exactly why the code is not working in your file.

    Narayan
  7. Luke M

    Luke M Excel Ninja

    Messages:
    9,288
    My guess is that you used the regular sheet name, and not the code name.
    Code (vb):
    If ((Sh.CodeName = "Sheet2") Or (Sh.CodeName = "Sheet3")) Then
    The code name is the name the VB model uses to refer to sheet. When looking in the VBE project explorer window, you can see usually two names for a sheet.
    upload_2015-2-16_12-43-15.png
    "Regular Name" is what you would see if you were in the workbook. However, in Narayan's code, you want to use "CodeName".
  8. Shazz

    Shazz Member

    Messages:
    125
    Hi,

    Please see attachment, I would really appreciate if you could identify what I am doing wrong, I am very new to all this, I also assume I have added to the codes correctly for additional sheets.

    Shazz
    xx

    Attached Files:

  9. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,591
    Hi ,

    See if this is OK.

    Narayan

    Attached Files:

    nkunni and Thomas Kuriakose like this.
  10. Shazz

    Shazz Member

    Messages:
    125
    That worked a treat, thanks so much.

    Shazz
    xx
  11. Meldj92

    Meldj92 New Member

    Messages:
    1
    Hi All,

    Having some trouble getting an almost identical function to work on my spreadsheet. Please see attached. Can anybody help me?

    I basically want sheets 'April 2015' and 'the Big Picture' ONLY to show as default.

    When you click a name on 'April 2015' it needs to unhide the sheet for that engineer. From that engineer sheet, you need to be able to navigate to all other sheets and hide all inactive sheets. I.e. at ANY given time, only 'April 2015' 'The Big Picture' and ONE 'Engineers name' sheets should be showing. Any ideas?

    Kind Regards,

    Jake

    Attached Files:

  12. nertil1

    nertil1 New Member

    Messages:
    1
    Hi all,

    Im trying to do the same that Shazz is doing but my hyperlink is based on a dropdown list. If you see the attached files, the Actuator link works just as was mentioned previously...the sheet hides and unhides but when I use the "go to tab" link the link does not work with the Actuator tab hidden. If you show the actuator tab, the link works the first time but then can't link again to it once it hides.

    Can someone help please??

    Attached Files:

  13. Jahangir Alam

    Jahangir Alam New Member

    Messages:
    3
    What is the error here.
    I want to hide all sheets except home page.

    Attached Files:

  14. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,591
    Hi ,

    See if this is what you wanted.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.
  15. Jahangir Alam

    Jahangir Alam New Member

    Messages:
    3
    Thank You very much Mr. Narayan. I was really asking for this.

    Can you please help me one more time. I have some further requirement.

    1. I want a useform to login multi ID and password.
    2. There will be a page for log in ID and password creating So that I can monitor my workbook user.

    Attached Files:

  16. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,591
    Hi ,

    I may or may not be able to help you , but since your question is unrelated to this thread , please start a new thread and post your question there.

    Narayan
  17. Jahangir Alam

    Jahangir Alam New Member

    Messages:
    3
    In that case please help me on below issue only.

    One sheet is missing, "Machine wise Skill". Can you please reupload by showing this in home menu?

    Attached Files:

  18. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,591
    Hi ,

    See this file.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.
  19. rubix

    rubix New Member

    Messages:
    4
    Im sorry im going over your posts and im trying to understand. Im trying to get rid of majority of the tabs on bottom (you may see some bad hyperlinks and formulas because I had to delete some tabs to upload)

    The very first post that I saw on here you click on the hyperlink and it popped up page 2/3 and made it disappear and its exactly what im trying to do. I just cannot seem to get it to work. When I pull the file to look at the macros (first time doing macro's btw) it doesn't show me the macro in the system so im just relying on posts. Can you help please?

    Thank you

    Attached Files:

  20. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,591
    Hi ,

    You need to indicate which is your Home tab , which will always be visible , and from where all other tabs will be accessed.

    You need to create the hyperlinks to the various tabs , and provide hyperlinks on each of those tabs to return to the Home tab.

    Once this is done , copy the following 2 macros to the ThisWorkbook section in the Visual Basic editor , and save the file as a .xlsm file.

    Narayan
    nkunni and Thomas Kuriakose like this.
  21. rubix

    rubix New Member

    Messages:
    4
    Awesome thank you very much it was very helpful. I only have 1 issue now and I have ALOT!! of tabs to do this do. Do I just keep continuing the or sheet#

    [ upload_2016-6-9_9-19-43.png

    I tried it with a few sheets but it was telling me

    upload_2016-6-9_9-20-52.png

    Thank you for all your help this will make me a very happy person completing this.
  22. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,591
    Hi ,

    My mistake ; the first macro should be changed to :
    Code (vb):

    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
                If (Sh.CodeName <> "Sheet1") Then
                  Sh.Visible = xlSheetVeryHidden
                End If
    End Sub
     
    This assumes Sheet1 is your Home tab which will always be visible.

    If it is not , replace the reference to Sheet1 with the codename of the sheet which is your Home tab.

    Narayan
    Thomas Kuriakose likes this.
  23. rubix

    rubix New Member

    Messages:
    4
    Sheet 1 is my hometab so fixed that but I still get the runtime error I get the first 2 sheets I want hidden but once I touch Sheet6 or Sheet7 I get the runtime error

    Maybe having the full window up will help. Im looking to hid all but maybe 4 total sheets Which would be Sheets 1 (main) sheet2 sheet3 and sheet 21(which I can always hide if necessary) but listed below I can get Sheet4 and Sheet5 to work with this but nothing else. Sorry im having this much trouble with it thank you for your help.
    upload_2016-6-9_10-1-50.png
  24. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,591
    Hi ,

    You have not replaced the first macro with what I posted ; is the error being generated by the first macro or the second ?

    Narayan
  25. rubix

    rubix New Member

    Messages:
    4
    If I hit debug this shows. I misunderstood so I replaced it correctly this time I think.

    still same outcome as before after the DIN and ISO tabs (sheets 4 and 5) it still bugs out.

    I can remove some tabs if needed and reattach the file. there are hyperlinks to and from the tabs im working on.

    upload_2016-6-9_10-25-46.png

Share This Page