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

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

"muziq2 " wrote in message ...
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


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

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 " wrote in message ...
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

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

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

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

"muziq2 " wrote in message ...
I'm using Excel '97. If I place the code in the worksheet and someone
has Excel 2002 or Excel 2000 - will it work.

Is there a workaround for Excel '97?

Thanks,

Jeff


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

Jim,

Thanks a lot for your help. I'll test it on another pc in the office.

-j

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

On Thursday, April 8, 2004 at 9:28:45 PM UTC+5:30, muziq2 < wrote:
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


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


Hello.. Here is my question .. what if i am using google sheets and i want to put hyperlink on hidden sheet .. why it is asking to unhide the sheet first and then click on Hyperlink.

On Thursday, April 8, 2004 at 9:28:45 PM UTC+5:30, muziq2 < wrote:
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


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


Hello.. Here is my question .. what if i am using google sheets and i want to
put hyperlink on hidden sheet .. why it is asking to unhide the sheet first
and then click on Hyperlink.

Why do you think you should be able to click a hyperlink if you can't see it?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion