Hyperlinks with hidden worksheets
 

Good afternoon all,

I have a workbook containing several worksheets.
I have included an Introduction Worksheet with Hyperlinks to each sheet.
However, I would like to be able to have the worksheets hidden (or very hidden),
except the Introduction sheet, until the Hyperlink is clicked, then that sheet is activated,
and when exited (by command button), this sheet becomes hidden again, and returns the user to
the Introduction sheet.

I hope I have expalained this well enough. I have some knowledge of VBA,( I have already set up the Command Buttons on the
worksheets).
Any help on this matter would be most appreciated.

Thank you

Mathew

Mathew,

It is not possible to hyperlink to a hidden sheet.
Suggest you look into adding buttons someplace on the Introduction
worksheet or adding unformatted rectangles directly above the
sheet names. Each button/rectangle would run a macro
to unhide the sheet.

Jim Cone
San Francisco, USA


"MPB" wrote in message
...
Good afternoon all,

I have a workbook containing several worksheets.
I have included an Introduction Worksheet with Hyperlinks to each sheet.
However, I would like to be able to have the worksheets hidden (or very hidden),
except the Introduction sheet, until the Hyperlink is clicked, then that sheet is activated,
and when exited (by command button), this sheet becomes hidden again, and returns the user to
the Introduction sheet.

I hope I have expalained this well enough. I have some knowledge of VBA,( I have already set up the Command Buttons on the
worksheets).
Any help on this matter would be most appreciated.

Thank you

Mathew

The hyperlink to the hidden worksheet won't work. How about just putting a
button from the forms toolbar right next to the worksheet you want to see.

Option Explicit
Sub BTNClick()
Dim BTN As Button
Dim wks As Worksheet

Set BTN = ActiveSheet.Buttons(Application.Caller)

Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(BTN.TopLeftCell.Offset(0, -1).Value)
On Error GoTo 0

If wks Is Nothing Then
Beep 'error
Else
wks.Visible = xlSheetVisible
Application.Goto wks.Range("a1"), scroll:=True
End If

End Sub

I put my worksheet names in column A and the button in column B (of the same
row). Then I assigned all the buttons the same macro.

(I'd also use a button from the forms toolbar for each of the "return to the
index sheet" buttons. Then I could use just one macro for each of these, too.)

MPB wrote:

Good afternoon all,

I have a workbook containing several worksheets.
I have included an Introduction Worksheet with Hyperlinks to each sheet.
However, I would like to be able to have the worksheets hidden (or very hidden),
except the Introduction sheet, until the Hyperlink is clicked, then that sheet is activated,
and when exited (by command button), this sheet becomes hidden again, and returns the user to
the Introduction sheet.

I hope I have expalained this well enough. I have some knowledge of VBA,( I have already set up the Command Buttons on the
worksheets).
Any help on this matter would be most appreciated.

Thank you

Mathew

--

Dave Peterson

Sorry Guys, but you are both wrong. I have just managed to get this to work
using Hyperlinks. It is a little tricky, but here goes:

The address for you hyperlink needs to be linked to itself. To acheive this
(Excel 2000):

Edit the hyperlink
Select "Place in This Document"
Enter the cell address in the Cell Reference (e.g. if you hyperlink is in
cell B6, type "B6"
Click OK.

In your Main excel sheet, click Alt-F11 to go to the VBA side of things.
In the Excel objects, right click on the main sheet and select "View Code"
In the code sheet, add:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
TmpRef = Chr(ActiveCell.Column + 64) & ActiveCell.Row
If Not Intersect(Target.Parent, Range(TmpRef)) Is Nothing Then
Call Unhide_Sheet
End If
End Sub

Don't ask me how it works, but it does!
Within your module, you can write a macro (mine is called "Unhide_Sheet") to
unhide the target sheet (see my code below)

Sub Unhide_Sheet()
Sheet_Ref = ActiveCell.Offset(0, -1).Value
Sheets(Format(Sheet_Ref, "")).Visible = True
Sheets(Format(Sheet_Ref, "")).Select
Sheets("Summary").Visible = False
Range("A6").Calculate
Range("A1").Select
End Sub

Essentially, whatever my hyperlink is called, Excel will unhide that sheet
and hide my main page (called "Summary")

Hope this helps, but if not, contact me as below:

Richard Massey
Simple Excel User!




"MPB" wrote:

Good afternoon all,

I have a workbook containing several worksheets.
I have included an Introduction Worksheet with Hyperlinks to each sheet.
However, I would like to be able to have the worksheets hidden (or very hidden),
except the Introduction sheet, until the Hyperlink is clicked, then that sheet is activated,
and when exited (by command button), this sheet becomes hidden again, and returns the user to
the Introduction sheet.

I hope I have expalained this well enough. I have some knowledge of VBA,( I have already set up the Command Buttons on the
worksheets).
Any help on this matter would be most appreciated.

Thank you

Mathew

I don't think you need to point the link at the same cell. You could actually
point at the hidden worksheet.

Option Explicit
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim testRng As Range
If Intersect(Target.Parent, Me.Range("a:a")) Is Nothing Then
Exit Sub
End If

Set testRng = Nothing
On Error Resume Next
Set testRng = Application.Range(Target.Parent.Value)
On Error GoTo 0

If testRng Is Nothing Then
'do nothing
Else
testRng.Parent.Visible = xlSheetVisible
Application.Goto testRng, scroll:=True
End If

End Sub

The Link pointed at sheet2 (hidden) range A1.
The value in the cell was: Sheet2!A1

==
But, personally, I wouldn't classify this as work done by the hyperlink. I'd
say it was the code that did the work. And if your going to use code, why not
make it a little more straight forward and just use the code directly.

Rich wrote:

Sorry Guys, but you are both wrong. I have just managed to get this to work
using Hyperlinks. It is a little tricky, but here goes:

The address for you hyperlink needs to be linked to itself. To acheive this
(Excel 2000):

Edit the hyperlink
Select "Place in This Document"
Enter the cell address in the Cell Reference (e.g. if you hyperlink is in
cell B6, type "B6"
Click OK.

In your Main excel sheet, click Alt-F11 to go to the VBA side of things.
In the Excel objects, right click on the main sheet and select "View Code"
In the code sheet, add:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
TmpRef = Chr(ActiveCell.Column + 64) & ActiveCell.Row
If Not Intersect(Target.Parent, Range(TmpRef)) Is Nothing Then
Call Unhide_Sheet
End If
End Sub

Don't ask me how it works, but it does!
Within your module, you can write a macro (mine is called "Unhide_Sheet") to
unhide the target sheet (see my code below)

Sub Unhide_Sheet()
Sheet_Ref = ActiveCell.Offset(0, -1).Value
Sheets(Format(Sheet_Ref, "")).Visible = True
Sheets(Format(Sheet_Ref, "")).Select
Sheets("Summary").Visible = False
Range("A6").Calculate
Range("A1").Select
End Sub

Essentially, whatever my hyperlink is called, Excel will unhide that sheet
and hide my main page (called "Summary")

Hope this helps, but if not, contact me as below:

Richard Massey
Simple Excel User!


"MPB" wrote:

Good afternoon all,

I have a workbook containing several worksheets.
I have included an Introduction Worksheet with Hyperlinks to each sheet.
However, I would like to be able to have the worksheets hidden (or very hidden),
except the Introduction sheet, until the Hyperlink is clicked, then that sheet is activated,
and when exited (by command button), this sheet becomes hidden again, and returns the user to
the Introduction sheet.

I hope I have expalained this well enough. I have some knowledge of VBA,( I have already set up the Command Buttons on the
worksheets).
Any help on this matter would be most appreciated.

Thank you

Mathew



--

Dave Peterson