A harder Hyperlink question.

[kane]

In my workbook I only want one worksheet visible at a time.

I've got several hyperlinks on the worksheet"Main" all refering to their own pages within the same workbook.

How do I get the "production" hyperlink on the "main"worksheet to:
1. Goto the "production" worksheet
2. Make the "production" worksheet the only visible worksheet?

[johnske]

Hi Kane,

There's no need to go to all the trouble of assigning hyperlinks to cells to do this, we can use workbook and worksheet level events to do it all - provided you're not assigning the hyperlinks to some shapes.

The code below creates a list (or Index) of all the sheets in column 1 of every worksheet, to go to that sheet you only need select the cell with the sheets name you want and all the others will be hidden. Try this on an empty workbook first to see if it's what you're after...(EDIT: It goes into the 'ThisWorkbook' module)

If you only want the Sheet Index on Sheet1 and a single cell 'link' on each sheet to take you back to the Index sheet, this can be easily modified to do it that way...

Option Explicit
 
Private Sub Workbook_Open()
Dim Sheet As Worksheet, N&
Application.ScreenUpdating = False
For Each Sheet In Worksheets
Sheet.Visible = xlSheetVisible
Sheet.Activate
Columns(1).Clear
[A1] = "SHEETS"
For N = 1 To Sheets.Count
[A65536].End(xlUp).Offset(1, 0) = Sheets(N).Name
Next N
Next Sheet
Sheet1.Activate
End Sub
 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Sheet As Worksheet
On Error Resume Next
For Each Sheet In Worksheets
If Sheet.Name <> ActiveSheet.Name Then
Sheet.Visible = xlSheetVeryHidden
End If
Next
End Sub
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim Sheet As Worksheet
On Error Resume Next
If Target.Column <> 1 Then Exit Sub
If Target.Text = Sheet.Name Then
Sheets(Target.Text).Visible = xlSheetVisible
Sheets(Target.Text).Activate
Else
Exit Sub
End If
End Sub

HTH,
John

[kane]

Hey John, I got your code to work, thx, but I really need the hyperlinks to send me to the next page. My main page has alot of summary data and the hyperlinks are spread out.

[johnske]

Hey John, I got your code to work, thx, but I really need the hyperlinks to send me to the next page. My main page has alot of summary data and the hyperlinks are spread out.

So, on each sheet you only want to go to the very next sheet? Could A1 be used for this? (you can use freeze frames to keep it in the same position when you scroll thru a sheet).

John

[kane]

Here is an example file.

[johnske]

Ok, will have a look 2morrow (if somone else doesn't do it 1st)

[Bob Phillips]

This seems to do what you want. It goes in ThisWorkbook, and is all that is needed, no activate code or anything

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Dim this As Worksheet
Dim oSheet As Worksheet
Set this = Worksheets(Range(Target.SubAddress).Parent.Name)
    this.Visible = xlSheetVisible
    For Each oSheet In Me.Worksheets
        If oSheet.Name <> this.Name Then
            oSheet.Visible = xlSheetHidden
        End If
    Next oSheet
    Application.Goto Range(Target.SubAddress)
End Sub

[kane]

Ahaa, that is it. Exacly what I wanted. Thanks so much.