Hide Sheets with a click of a button

[zac_595]

Hi,

I have a workbook with 13 Sheets, which is pretty much a daily report.
Sheet one is called Navigation calendar. This sheet has all the hyperlinks to navigate to each month which are the 12 sheets, Jan to Dec and are hidden. Now if I get one sheet right then the rest should work.

In the "navigational calendar" I have dates that would point out to a day which would unhide that particular sheet.
Ex: Jan 1 is hyperlinked, I click it and it unhides Sheet 2 - which is named "January". Now I have figured out the code on how to unhide while clicking the hyperlink from the parent sheet (Navigational Calendar).
The problem here is I want to create a macro, since I have buttons in the remaining 12 sheets. When I click the button "Back" or "go to navigational calendar" I want the current sheet to hide and go to the target page that it is hyperlinked to.

All the 12 sheets will be hidden, I was able to figure out the first part. Going to the Parent sheet and unhiding it by clicking the hyperlink.


Code to Unhide when clicking a hyperlink

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.Goto ActiveCell, True
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
Application.Goto ActiveCell, True
End If


End Sub

for the second part I Need to run a macro to hide by clicking a button that says "BACK", which is where I am blank.

Thank you. It would be great if I could get this to work on my reports.

 

[wigi]

Hello

Did you see this topic:
http://www.mrexcel.com/forum/excel-questions/731030-hyperlink-open-hidden-worksheets.html

It dates from... yesterday...

 

[zac_595]

Hi Wigi,
Thanks for looking into this and thank you for guiding me through that link you provided there, but what I want is a little different here. I have shapes in the 12 sheets(Back, Next etc) which has to be hyperlinked to the parent sheet. Now each time I click on to those shapes, the sheets should hide. I just need a code for that so I can run it. I have something like this, but it does not work.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Worksheets("Navigational Calendar Daily").Select
Target.Parent.Worksheet.Visible = False
    Application.Goto ActiveCell, True
End Sub

Gives me an error 9 "Subscript out of range". Need something similar so that can be assigned to a shape while it hyperlinks and hides a sheet once the shape is clicked on.

 

[zac_595]

Besides that, the unhide thing works from the parent to the 12 sheets, i.e if the 12 sheets are hidden, but to hide them again is where the problem is.
Thank you.

 

[wigi]

thank you for guiding me through that link you provided there, but what I want is a little different here.

I'm sorry to say so, but unfortunately you did not into my suggestion in the other topic: using the Worksheet_Deactivate event.

For example, for 1 of the 12 sheets to be hidden:

Private Sub Worksheet_Deactivate()
    Me.Visible = xlSheetHidden
End Sub

 

[zac_595]

Hi Wigi,
Thank you for the code and suggestions, the code works perfectly, I did look into the link that you gave regarding the topic similar to mine but that was not what i exactly wanted, and I tried using the code and that did not work as well. How ever this works fine and I am really thankful for that. Thanks again... cheers!!.
Your help is really appreciated.