PDA

View Full Version : [SOLVED] A harder Hyperlink question.



kane
05-19-2005, 05:55 PM
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
05-19-2005, 07:47 PM
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
05-20-2005, 06:48 AM
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
05-20-2005, 06:57 AM
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
05-20-2005, 07:49 AM
Here is an example file.

johnske
05-20-2005, 07:58 AM
Ok, will have a look 2morrow (if somone else doesn't do it 1st)

Bob Phillips
05-20-2005, 08:06 AM
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
05-20-2005, 08:17 AM
Ahaa, that is it. Exacly what I wanted. Thanks so much.:clap: :beerchug: