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

  • 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

Lim Aina

New Member
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!
 

Attachments

  • Dashboard to Link To Hidden Sheet Test.xlsm
    326.1 KB · Views: 127
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!
 
Hi Lim ,

There are 2 macros in the ThisWorkbook section :
Code:
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
 
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
 
My guess is that you used the regular sheet name, and not the code name.
Code:
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".
 
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
 

Attachments

  • Hidden Links Test.xlsm
    196.5 KB · Views: 76
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
 

Attachments

  • Engineer KPIs TAKE2.xlsm
    325.8 KB · Views: 51
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??
 

Attachments

  • Est draft 2015 04-02 - 0000.xlsm
    88.7 KB · Views: 26
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.
 

Attachments

  • Worker Skill Matrixraa.xlsm
    718.1 KB · Views: 9
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
 
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?
 

Attachments

  • Worker Skill Matrixraa.xlsm
    732.2 KB · Views: 5
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
 

Attachments

  • Request Form test.xlsx
    137.6 KB · Views: 17
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
 
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.
 
Hi ,

My mistake ; the first macro should be changed to :
Code:
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
 
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
 
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
 
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
 
Back
Top