Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlinks With Workbook With Hidden Sheets | Excel Discussion (Misc queries) | |||
How do I print only the hidden worksheets in a workbook? | Excel Discussion (Misc queries) | |||
How to protect and unprotect 30 worksheets in a file every month . | Excel Worksheet Functions | |||
Protect/unprotect all worksheets | Excel Worksheet Functions | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) |