Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 8th 04, 04:58 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2004
Posts: 1
Default Hyperlink to Hidden sheet

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


  #2   Report Post  
Old April 8th 04, 06:25 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,290
Default Hyperlink to Hidden sheet


"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/

  #3   Report Post  
Old April 8th 04, 06:35 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,290
Default Hyperlink to Hidden sheet

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


  #4   Report Post  
Old April 8th 04, 10:12 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2004
Posts: 1
Default Hyperlink to Hidden sheet

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

  #5   Report Post  
Old April 9th 04, 12:06 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,290
Default Hyperlink to Hidden sheet

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/



  #6   Report Post  
Old April 9th 04, 12:16 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2004
Posts: 1
Default Hyperlink to Hidden sheet

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

  #7   Report Post  
Old August 1st 19, 07:28 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2019
Posts: 1
Default Hyperlink to Hidden sheet

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.
  #8   Report Post  
Old August 1st 19, 11:47 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,042
Default Hyperlink to Hidden sheet

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
hidden Hyperlink expect_ed Excel Discussion (Misc queries) 1 November 11th 09 08:06 PM
how to give hyperlink to hidden excel sheet Chetu Excel Worksheet Functions 0 April 9th 09 08:56 AM
Hyperlink to hidden cells Excel Novice Excel Discussion (Misc queries) 1 November 13th 08 04:59 PM
I need my Hidden Rows to stay hidden when I print the sheet. Rosaliewoo Excel Discussion (Misc queries) 2 July 20th 06 07:51 PM
Saving hidden data with a worksheet (preferably without using a hidden sheet) Dick Kusleika[_3_] Excel Programming 2 January 21st 04 04:39 PM


All times are GMT +1. The time now is 12:22 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017