Hyperlink To Hidden Worksheet
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
Hide Worksheet Automatically
Private Sub Worksheet_Activate()
Me.Visible = xlSheetVisible
End Sub
Private Sub Worksheet_Deactivate()
Me.Visible = xlSheetVeryHidden
End Sub
Gladwin Ram wrote:
Dealing with Hidden Sheets
03-Nov-09
Hi Chip,
Thank you for the code to open hidden sheets through hyperlinks. It works well. Is there a way to hide the sheet back once I'm done working on that opened hidden sheet?
Previous Posts In This Thread:
Hidden Worksheets
Hi al
Is it possible to have Hyperlinks on one worksheet to access Hidde
Worksheets on the same work boo
Thanks for lookin
Stew
You need to use some code to make the target worksheet visible and thecall the
You need to use some code to make the target worksheet visible and th
call the hyperlink a second time. Right-click the tab of the workshee
than contains the hyperlink (not the sheet to which the hyperlin
refers), and paste in the following code
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink
Dim WB As Workboo
Dim WSName As Strin
Dim WS As Workshee
Dim N As Lon
With Targe
If .Address = vbNullString The
Set WB = ThisWorkboo
Els
Set WB = Workbooks(.Address
End I
N = InStr(1, .SubAddress, "!"
WSName = Replace(Left(.SubAddress, N - 1), "'", vbNullString
Set WS = WB.Worksheets(WSName
If WS.Visible <> xlSheetVisible The
Application.EnableEvents = Fals
WS.Visible = xlSheetVisibl
Target.Follo
Application.EnableEvents = Tru
End I
End Wit
End Su
If the worksheet to which the hyperlink refers is hidden, the cod
unhides the worksheet and then calls the hyperlink's Follow method t
go to the link on the now-visible worksheet
Cordially
Chip Pearso
Microsoft Most Valuable Professiona
Excel Product Group, 1998 - 200
Pearson Software Consulting, LL
www.cpearson.co
(email on web site)
Hi ChipRight clip on the tabthen I assume View codeThen paste in MacroIs there
Hi Chi
Right clip on the ta
then I assume View cod
Then paste in Macr
Is there anything else I need to d
I did that on my Hyperlink sheet and then hid two of my sheets and tried t
accesss them with the hyperlinks. No go. Unhide them, all o
I am not very familiar with macros but you have the Gist of what I want t
achieve
What am I missin
Bes
Ste
:
Your procedures for using code seem to be fine.
Your procedures for using code seem to be fine. I tested the code i
XL2003 and XL2007. What version are you using? Also, the code work
only with Hyperlinks created via the Insert menu. It will not wor
with hyperlinks that are created with the HYPERLINK worksheet functio
-- clicking on a link created with the HYPERLINK worksheet functio
does not raise an event so there is no way to unhide the sheet
Cordially
Chip Pearso
Microsoft Most Valuable Professiona
Excel Product Group, 1998 - 200
Pearson Software Consulting, LL
www.cpearson.co
(email on web site)
Hi ChipAnd there lies the problem.
Hi Chi
And there lies the problem. I am using the Hyperlink as a worksheet Functio
and not as an insert. Thank you for your Time
Bes
Stewar
:
Dealing with Hidden Sheets
Hi Chip,
Thank you for the code to open hidden sheets through hyperlinks. It works well. Is there a way to hide the sheet back once I'm done working on that opened hidden sheet?
Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint - Managing Unused or Archive sites automatically
http://www.eggheadcafe.com/tutorial...5b-a2a8deb60cad/sharepoint--managing-unu.aspx