Hyperlink to Hidden sheet

M

muziq2

Hi all,

I need to hyperlink to a hidden sheet inside the same workbook. M
hyperlink format is
=HYPERLINK("[filename]'sheetname'!A1","friendlyname")

The exact hyperlink is below.
=HYPERLINK("[0304HireTerm.xls]'Job Elimination'!A1","Job elimination")

The hyperlink function only works when the sheet is not hidden.
Someone provided me with the code below saying that it will open th
hidden sheet however I can't get it to work. Any other suggestions.
There are about 5 sheets that I want hidden until someone clicks on
link to the sheet for more background detail.

Thanks,
Jeff

"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 Su
 
J

Jim Cone

muziq2 > said:
Hi all,

I need to hyperlink to a hidden sheet inside the same workbook. My
hyperlink format is
=HYPERLINK("[filename]'sheetname'!A1","friendlyname")

The exact hyperlink is below.
=HYPERLINK("[0304HireTerm.xls]'Job Elimination'!A1","Job elimination")

The hyperlink function only works when the sheet is not hidden.
Someone provided me with the code below saying that it will open the
hidden sheet however I can't get it to work. Any other suggestions.
There are about 5 sheets that I want hidden until someone clicks on a
link to the sheet for more background detail.

Thanks,
Jeff

"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
Click to expand...
 
J

Jim Cone

Jeff,

XL 97 does not have a FollowHyperlink event.
XL 2002 does. XL2000 ?

However the code would not work in XL 2002 until the single quote
marks "'" around the sheet name were removed.
The following worked for me on hidden sheets...
'--------------------------------------------
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim strAddress As String

strAddress = _
Application.Substitute(Target.SubAddress, "'", vbNullString)
Worksheets(Left$(strAddress, _
InStr(1, strAddress, "!") - 1)).Visible = xlSheetVisible
Application.EnableEvents = False
Target.Follow
Application.EnableEvents = True

End Sub
'--------------------------------------------

Regards,
Jim Cone
San Francisco, CA

muziq2 > said:
Hi all,
I need to hyperlink to a hidden sheet inside the same workbook. My
hyperlink format is
=HYPERLINK("[filename]'sheetname'!A1","friendlyname")
The exact hyperlink is below.
=HYPERLINK("[0304HireTerm.xls]'Job Elimination'!A1","Job elimination")
The hyperlink function only works when the sheet is not hidden.
Someone provided me with the code below saying that it will open the
hidden sheet however I can't get it to work. Any other suggestions.
There are about 5 sheets that I want hidden until someone clicks on a
link to the sheet for more background detail.
Thanks,
Jeff
Click to expand...
 
M

muziq2

I'm using Excel '97. If I place the code in the worksheet and someon
has Excel 2002 or Excel 2000 - will it work.

Is there a workaround for Excel '97?

Thanks,

Jef
 
J

Jim Cone

Jeff,

It works in both Excel 97 and Excel 2002.
I pasted the code, as is, into a worksheet code module in XL97.
The code was completely ignored.(that's good).
Of course hidden sheets could not be shown.
I saved the workbook and then opened it is XL2002 and it worked.
Hidden sheets were shown when hyperlinks were clicked.
"I would have never thunk it."

It would probably be a good idea to test it yourself before giving it
to the boss.

As for a workaround for XL97 - there probably is, but I have
run out of time to work on it.

Regards,
Jim Cone
San Francisco, CA
 
Top