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