Unhide and hide sheet using hyperlink

[russelldt]

Hello. I have a master tab (Index) which contains a number of hyperlinks to other sheets (in the same file). All the sheets are hidden. I would like to be able to click the hyperlinked to unhide a sheet , and then when I exit the file, all the unhidden sheets (except the Index) are hidden.

Can anyone help me with this,

Thanks

 

[Micron]

Create your hyperlinks - AFAIK it won't matter which sheet you pick as the target for the hyperlinks but you might as well pick the sheet name that the link will point to. The text to display should be the name of the sheet you want to unhide.

In workbook module to hide all but "Index" at closing try:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

For Each ws In Sheets
   If ws.Name <> "Index" Then ws.Visible = False
Next

End Sub

On your Index sheet module create procedure

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Sheets(Target.Name).Visible = True

End Sub

 

[Micron]

I think this should be

For Each ws In Worksheets

 

[russelldt]

Create your hyperlinks - AFAIK it won't matter which sheet you pick as the target for the hyperlinks but you might as well pick the sheet name that the link will point to. The text to display should be the name of the sheet you want to unhide.

In workbook module to hide all but "Index" at closing try:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

For Each ws In Sheets
   If ws.Name <> "Index" Then ws.Visible = False
Next

End Sub

On your Index sheet module create procedure

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Sheets(Target.Name).Visible = True

End Sub

Perfect, many thanks Micron

 

[Micron]

You're welcome. If it worked for you, can you mark this one as solved?
Thanks.

 

[russelldt]

You're welcome. If it worked for you, can you mark this one as solved?
Thanks.

Sure

 

[Micron]

Well, that's interesting that you marked your acceptance of the solution using your thank you post!

 

[russelldt]

Sorry. I have marked the correct one

 

[Micron]

NP. Just trying to double my pay rate here by accumulating brownie points.