Open Hidden Worksheet
 

Hi,

I want to hyperlink to a hidden worksheet in the same workbook. An
ideas?

Everything works fine when the sheet that I want the hyperlink to jum
to is not hidden. I use the following function.

=HYPERLINK("[filename]'sheetname'!A1","display name")

When the user clicks on this cell the other sheet is displayed.

However, I want the destination sheet to be hidden until the use
clicks on the cell containing the hyperlink. Then I want the sheet t
automatically unhide and display. Then when the user clicks o
another workbook the destination sheet should then revert to hidden.

Is this possible?

Thanks,

Jef

--
Message posted from http://www.ExcelForum.com

Open Hidden Worksheet
 

You need to handle two different events. Put this code in the worksheet module that has the hyperlink(s)

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink
Worksheets(Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)).Visible = xlSheetVisibl
Application.EnableEvents = Fals
Target.Follo
Application.EnableEvents = Tru
End Su

And put this code in the Worksheet module of the worksheet that you want to hide when deactivated

Private Sub Worksheet_Deactivate(
Me.Visible = xlSheetHidde
End Su

This functionality is kinda "rigged". The FollowHyperlink event is invoked twice, with the second one turned off to prevent a sort of recursion. The first pass to make the sheet visible, and the second pass to follow the hyperlink, since the first pass will not work on an invisible worksheet. Which indicates that the Follow method of the Hyperlink object is executed prior to the firing of the FollowHyperlink event. That's kinda silly, but there it is

The rigging that I put in there will not work if the sheet name you've hidden has an Exclamation point in it. If you do you have a bang in there, you'll need to make a function to return the target sheet name from the subaddress that is a little more robust than the bungle I put in there. Post back if you need help with that

I hope that helps

-Brad Vontur

Open Hidden Worksheet
 

Thanks Brad. The sheets now hide automatically. However, I can't ge
the other code to work. I don't have an exclamation point in the shee
name however the link text is below and does contain an exclamatio
point.
=HYPERLINK("[0304HireTerm.xls]'VoluntaryTerm'!A1","Voluntary Term")

Will that keep the code from working? Is there a workaround.

Thanks,

Jef

--
Message posted from http://www.ExcelForum.com

Open Hidden Worksheet
 

Brad was kind enough to provide the code below to hyperlink to a hidde
sheet. However it is not working. The link only works when the shee
I'm linking to is not hidden. Please help.

"Put this code in the worksheet module that has the hyperlink(s).

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Worksheets(Left(Target.SubAddress, InStr(1, Target.SubAddress, "!")
1)).Visible = xlSheetVisible
Application.EnableEvents = False
Target.Follow
Application.EnableEvents = True
End Sub


--
Message posted from http://www.ExcelForum.com