Hi all
Is it possible to have Hyperlinks on one worksheet to access Hidden
Worksheets on the same work book

Thanks for looking
Stew

You need to use some code to make the target worksheet visible and the
call the hyperlink a second time. Right-click the tab of the worksheet
than contains the hyperlink (not the sheet to which the hyperlink
refers), and paste in the following code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim WB As Workbook
Dim WSName As String
Dim WS As Worksheet
Dim N As Long
With Target
If .Address = vbNullString Then
Set WB = ThisWorkbook
Else
Set WB = Workbooks(.Address)
End If

N = InStr(1, .SubAddress, "!")
WSName = Replace(Left(.SubAddress, N - 1), "'", vbNullString)
Set WS = WB.Worksheets(WSName)
If WS.Visible < xlSheetVisible Then
Application.EnableEvents = False
WS.Visible = xlSheetVisible
Target.Follow
Application.EnableEvents = True
End If
End With
End Sub

If the worksheet to which the hyperlink refers is hidden, the code
unhides the worksheet and then calls the hyperlink's Follow method to
go to the link on the now-visible worksheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sat, 12 Sep 2009 06:36:01 -0700, stew
wrote:

Hi all
Is it possible to have Hyperlinks on one worksheet to access Hidden
Worksheets on the same work book

Thanks for looking
Stew

Hi Chip

Right clip on the tab

then I assume View code

Then paste in Macro

Is there anything else I need to do

I did that on my Hyperlink sheet and then hid two of my sheets and tried to
accesss them with the hyperlinks. No go. Unhide them, all ok

I am not very familiar with macros but you have the Gist of what I want to
achieve.

What am I missing

Best

Stew

"Chip Pearson" wrote:

You need to use some code to make the target worksheet visible and the
call the hyperlink a second time. Right-click the tab of the worksheet
than contains the hyperlink (not the sheet to which the hyperlink
refers), and paste in the following code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim WB As Workbook
Dim WSName As String
Dim WS As Worksheet
Dim N As Long
With Target
If .Address = vbNullString Then
Set WB = ThisWorkbook
Else
Set WB = Workbooks(.Address)
End If

N = InStr(1, .SubAddress, "!")
WSName = Replace(Left(.SubAddress, N - 1), "'", vbNullString)
Set WS = WB.Worksheets(WSName)
If WS.Visible < xlSheetVisible Then
Application.EnableEvents = False
WS.Visible = xlSheetVisible
Target.Follow
Application.EnableEvents = True
End If
End With
End Sub

If the worksheet to which the hyperlink refers is hidden, the code
unhides the worksheet and then calls the hyperlink's Follow method to
go to the link on the now-visible worksheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sat, 12 Sep 2009 06:36:01 -0700, stew
wrote:

Hi all
Is it possible to have Hyperlinks on one worksheet to access Hidden
Worksheets on the same work book

Thanks for looking
Stew

Your procedures for using code seem to be fine. I tested the code in
XL2003 and XL2007. What version are you using? Also, the code works
only with Hyperlinks created via the Insert menu. It will not work
with hyperlinks that are created with the HYPERLINK worksheet function
-- clicking on a link created with the HYPERLINK worksheet function
doesn't raise an event so there is no way to unhide the sheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sat, 12 Sep 2009 23:43:01 -0700, stew
wrote:

Hi Chip

Right clip on the tab

then I assume View code

Then paste in Macro

Is there anything else I need to do

I did that on my Hyperlink sheet and then hid two of my sheets and tried to
accesss them with the hyperlinks. No go. Unhide them, all ok

I am not very familiar with macros but you have the Gist of what I want to
achieve.

What am I missing

Best

Stew

"Chip Pearson" wrote:

You need to use some code to make the target worksheet visible and the
call the hyperlink a second time. Right-click the tab of the worksheet
than contains the hyperlink (not the sheet to which the hyperlink
refers), and paste in the following code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim WB As Workbook
Dim WSName As String
Dim WS As Worksheet
Dim N As Long
With Target
If .Address = vbNullString Then
Set WB = ThisWorkbook
Else
Set WB = Workbooks(.Address)
End If

N = InStr(1, .SubAddress, "!")
WSName = Replace(Left(.SubAddress, N - 1), "'", vbNullString)
Set WS = WB.Worksheets(WSName)
If WS.Visible < xlSheetVisible Then
Application.EnableEvents = False
WS.Visible = xlSheetVisible
Target.Follow
Application.EnableEvents = True
End If
End With
End Sub

If the worksheet to which the hyperlink refers is hidden, the code
unhides the worksheet and then calls the hyperlink's Follow method to
go to the link on the now-visible worksheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sat, 12 Sep 2009 06:36:01 -0700, stew
wrote:

Hi all
Is it possible to have Hyperlinks on one worksheet to access Hidden
Worksheets on the same work book

Thanks for looking
Stew

Hi Chip

And there lies the problem. I am using the Hyperlink as a worksheet Function
and not as an insert. Thank you for your Time.

Best

Stewart

"Chip Pearson" wrote:

Your procedures for using code seem to be fine. I tested the code in
XL2003 and XL2007. What version are you using? Also, the code works
only with Hyperlinks created via the Insert menu. It will not work
with hyperlinks that are created with the HYPERLINK worksheet function
-- clicking on a link created with the HYPERLINK worksheet function
doesn't raise an event so there is no way to unhide the sheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sat, 12 Sep 2009 23:43:01 -0700, stew
wrote:

Hi Chip

Right clip on the tab

then I assume View code

Then paste in Macro

Is there anything else I need to do

I did that on my Hyperlink sheet and then hid two of my sheets and tried to
accesss them with the hyperlinks. No go. Unhide them, all ok

I am not very familiar with macros but you have the Gist of what I want to
achieve.

What am I missing

Best

Stew

"Chip Pearson" wrote:

You need to use some code to make the target worksheet visible and the
call the hyperlink a second time. Right-click the tab of the worksheet
than contains the hyperlink (not the sheet to which the hyperlink
refers), and paste in the following code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim WB As Workbook
Dim WSName As String
Dim WS As Worksheet
Dim N As Long
With Target
If .Address = vbNullString Then
Set WB = ThisWorkbook
Else
Set WB = Workbooks(.Address)
End If

N = InStr(1, .SubAddress, "!")
WSName = Replace(Left(.SubAddress, N - 1), "'", vbNullString)
Set WS = WB.Worksheets(WSName)
If WS.Visible < xlSheetVisible Then
Application.EnableEvents = False
WS.Visible = xlSheetVisible
Target.Follow
Application.EnableEvents = True
End If
End With
End Sub

If the worksheet to which the hyperlink refers is hidden, the code
unhides the worksheet and then calls the hyperlink's Follow method to
go to the link on the now-visible worksheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sat, 12 Sep 2009 06:36:01 -0700, stew
wrote:

Hi all
Is it possible to have Hyperlinks on one worksheet to access Hidden
Worksheets on the same work book

Thanks for looking
Stew

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?



Chip Pearson wrote:

Your procedures for using code seem to be fine.
13-Sep-09

Your procedures for using code seem to be fine. I tested the code in
XL2003 and XL2007. What version are you using? Also, the code works
only with Hyperlinks created via the Insert menu. It will not work
with hyperlinks that are created with the HYPERLINK worksheet function
-- clicking on a link created with the HYPERLINK worksheet function
does not raise an event so there is no way to unhide the sheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

Previous Posts In This Thread:

On Saturday, September 12, 2009 9:36 AM
stew wrote:

Hidden Worksheets
Hi all
Is it possible to have Hyperlinks on one worksheet to access Hidden
Worksheets on the same work book

Thanks for looking
Stew

On Saturday, September 12, 2009 12:54 PM
Chip Pearson wrote:

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 the
call the hyperlink a second time. Right-click the tab of the worksheet
than contains the hyperlink (not the sheet to which the hyperlink
refers), and paste in the following code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim WB As Workbook
Dim WSName As String
Dim WS As Worksheet
Dim N As Long
With Target
If .Address = vbNullString Then
Set WB = ThisWorkbook
Else
Set WB = Workbooks(.Address)
End If

N = InStr(1, .SubAddress, "!")
WSName = Replace(Left(.SubAddress, N - 1), "'", vbNullString)
Set WS = WB.Worksheets(WSName)
If WS.Visible < xlSheetVisible Then
Application.EnableEvents = False
WS.Visible = xlSheetVisible
Target.Follow
Application.EnableEvents = True
End If
End With
End Sub

If the worksheet to which the hyperlink refers is hidden, the code
unhides the worksheet and then calls the hyperlink's Follow method to
go to the link on the now-visible worksheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Sunday, September 13, 2009 2:43 AM
stew wrote:

Hi ChipRight clip on the tabthen I assume View codeThen paste in MacroIs there
Hi Chip

Right clip on the tab

then I assume View code

Then paste in Macro

Is there anything else I need to do

I did that on my Hyperlink sheet and then hid two of my sheets and tried to
accesss them with the hyperlinks. No go. Unhide them, all ok

I am not very familiar with macros but you have the Gist of what I want to
achieve.

What am I missing

Best

Stew

"Chip Pearson" wrote:

On Sunday, September 13, 2009 7:54 AM
Chip Pearson wrote:

Your procedures for using code seem to be fine.
Your procedures for using code seem to be fine. I tested the code in
XL2003 and XL2007. What version are you using? Also, the code works
only with Hyperlinks created via the Insert menu. It will not work
with hyperlinks that are created with the HYPERLINK worksheet function
-- clicking on a link created with the HYPERLINK worksheet function
does not raise an event so there is no way to unhide the sheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Sunday, September 13, 2009 8:15 AM
stew wrote:

Hi ChipAnd there lies the problem.
Hi Chip

And there lies the problem. I am using the Hyperlink as a worksheet Function
and not as an insert. Thank you for your Time.

Best

Stewart

"Chip Pearson" wrote:

EggHeadCafe - Software Developer Portal of Choice
FLASH! ECMA CERTIFIES C# and THE CLI !!!
http://www.eggheadcafe.com/tutorials...rtifies-c.aspx

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:

On Saturday, September 12, 2009 9:36 AM
stew wrote:

Hidden Worksheets
Hi all
Is it possible to have Hyperlinks on one worksheet to access Hidden
Worksheets on the same work book

Thanks for looking
Stew

On Saturday, September 12, 2009 12:54 PM
Chip Pearson wrote:

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 the
call the hyperlink a second time. Right-click the tab of the worksheet
than contains the hyperlink (not the sheet to which the hyperlink
refers), and paste in the following code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim WB As Workbook
Dim WSName As String
Dim WS As Worksheet
Dim N As Long
With Target
If .Address = vbNullString Then
Set WB = ThisWorkbook
Else
Set WB = Workbooks(.Address)
End If

N = InStr(1, .SubAddress, "!")
WSName = Replace(Left(.SubAddress, N - 1), "'", vbNullString)
Set WS = WB.Worksheets(WSName)
If WS.Visible < xlSheetVisible Then
Application.EnableEvents = False
WS.Visible = xlSheetVisible
Target.Follow
Application.EnableEvents = True
End If
End With
End Sub

If the worksheet to which the hyperlink refers is hidden, the code
unhides the worksheet and then calls the hyperlink's Follow method to
go to the link on the now-visible worksheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Sunday, September 13, 2009 2:43 AM
stew wrote:

Hi ChipRight clip on the tabthen I assume View codeThen paste in MacroIs there
Hi Chip

Right clip on the tab

then I assume View code

Then paste in Macro

Is there anything else I need to do

I did that on my Hyperlink sheet and then hid two of my sheets and tried to
accesss them with the hyperlinks. No go. Unhide them, all ok

I am not very familiar with macros but you have the Gist of what I want to
achieve.

What am I missing

Best

Stew

"Chip Pearson" wrote:

On Sunday, September 13, 2009 7:54 AM
Chip Pearson wrote:

Your procedures for using code seem to be fine.
Your procedures for using code seem to be fine. I tested the code in
XL2003 and XL2007. What version are you using? Also, the code works
only with Hyperlinks created via the Insert menu. It will not work
with hyperlinks that are created with the HYPERLINK worksheet function
-- clicking on a link created with the HYPERLINK worksheet function
does not raise an event so there is no way to unhide the sheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Sunday, September 13, 2009 8:15 AM
stew wrote:

Hi ChipAnd there lies the problem.
Hi Chip

And there lies the problem. I am using the Hyperlink as a worksheet Function
and not as an insert. Thank you for your Time.

Best

Stewart

"Chip Pearson" wrote:

On Tuesday, November 03, 2009 5:58 AM
Gladwin Ram wrote:

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/tutorials...aging-unu.aspx