How to Hide inactive worksheets when clicked on Main Sheet

[Johnchethan]

Hi, can anybody help to write a code which will hide worksheets which are inactive when i click on the Main worksheet and will open when i choose a particular name/word on the main sheet. When choose a name/word the data regarding that person/word should show up in the new sheet or even if the sheet which has the data or that person/name opens also would be fine. Please suggest me regarding this query.

 

[Hui]

Johnchethan


Firstly, Welcome to the Chandoo.org forums


Copy the 2 blocks of code below and insert it into the Worksheet module for the sheet you want as your "Main" sheet


On the main sheet I would use Hyperlinks to link to each sheet you want to change to


The Worksheet_Activate module will hide all worksheet except the sheet called "Main"

You can change the name to suit

[pre]

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

[/pre]

 

[Johnchethan]

Hi Hui,


Thank for the suggestion, however i am not able to understand what has to be changed when it comes to the hyperlink codes. Do i need to change the name "Mysheet", because when i tried it does not open the hidden sheet..please suggest me.