Unhide and Hide Worksheets with Hyperlinks

[bdiz]

Hello Chandoo Forumers!

Disclaimer: I know little to nothing about VBA but I know enough to know I need it!

My ultimate goal is to hide all worksheets except the main Dashboard when my file is opened and unhide each worksheet when a text box with a hyperlink is selected. The sheet should be hidden again once the "Return to Menu" text box is selected. The Main Dashboard can stay active all the time (or doesn't have to if that complicates matters). I think I would like to use very hidden so users can not simply unhide sheets.

I tried the below code from Hui which I found in a similar post (http://forum.chandoo.org/threads/ho...s-when-clicked-on-main-sheet.4640/#post-24566) but even after replacing Main with my worksheet name it gave a compile error (Expected: Expression) at the # before 60.

I have attached a sample file. My real file has probably 20 more worksheets but only included a few for simplicity.

Sub Worksheet_Activate()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "Main" Then ws.Visible = False 'Change this worksheet name to suit
Next
End Sub

The Worksheet_FollowHyperlink module will use the Link in the hyperlink to extract the Worksheet name and then use that to unhide the sheet and select it

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim mysheet As String
mysheet = Left(Target.Name, InStr(1, Target.Name, "!") - 1)
Worksheets(mysheet).Visible = True
Worksheets(mysheet).Activate
End Sub

I hope someone enjoys the challenge and can help out.

Thanks for your time,
B

 

[Hui]

Bdiz

firstly, Welcome to the Chandoo.org Forums

Please see attached file

I have not used Hyperlinks at all, purely shapes linked to macros

As well as Workbook_Open and Worksheet_Activate events on the Dashboard worksheet

 

[bdiz]

Hui,

Thank you for the welcome and quick response!

I love the approach. The sample works great, however, I was also hoping the worksheets could be very hidden so the user can not simply unhide them. I tried protecting the workbook in your sample file to prevent unhiding sheets but that generates a run time 1004 error: unable to set the visible property of the worksheet class. Is it possible to have the sheets very hidden or protect the workbook?

Thanks,
Brian

 

[bdiz]

Hui,

I was able to update the code to change all sheets to very hidden by using ws.Visible = xlSheetVeryHidden in Hide_All() instead of ws.Visible = False.

However, I am still interested in knowing if it is possible to protect the workbook and still have these macros work? Although someone would need have advanced knowledge of Excel, even if the sheets are very hidden, someone could still unhide them. I actually created an unhide all macro (with some help) to use for myself. Someone could do the same or use mine if I leave it in the workbook. It seems the only way to truly prevent someone from unhiding sheets is to password protect the workbook. Please correct me if I am wrong.

Thanks,
Brian

 

[Hui]

I have shifted the Hide All worksheets into the Before_Close workbook event
What this means is that it will hide all worksheets except the dashboard when the file closes
When the user opens the file all worksheets except the dashboard are Very Hidden
and if they don't enable VBA they won't have access

You can then also add a password to access VBA so that people can't see the worksheet names and hence can't unhide them

to do that got VBA, Toools, VBA Project properties, Protection Tab
Tick Lock project for Viewing and set a password

 

[bdiz]

Thank you Hui! I really appreciate the explanations along with the sample files; best way to learn.

Locking the VBA is just what I needed. I also inserted "Private" before the "Sub" to remove the macros from the macro list. Between the two I think it covers access to the macros.

However, when I add Private before the Hide_All code I receive a Compile Error: Sub or Funtion not Defined when returning to the Dashboard worksheet. I moved the the Hide_All code from Module1 to the code of the Dashboard worksheet and it works correctly. Curious as to whether this is the best method, what else it might impact, and why including Private only impacts Hide_All. Then I can probably leave you alone.

Brian

 

[bdiz]

Guess that is not the best method as I just got a compile error when closing the file.