PDA

View Full Version : Solved: Hyperlinks to Hidden Worksheets



SDave
08-28-2009, 05:29 AM
I currently have a workbook which contains 10 worksheets. Of those 10 I would like to have 6 hidden until such time a user clicks on a hyperlink.

Although it somewhat defeats the object hiding a sheet only to make it visible I would like to know if there is a VBA code which will enable me to perform the above?!

The six hyperlinks will reside in one single worksheet - let's say sheet1 for arguments sake.

I have previously done something somewhat similair but in that particular instance it was one hyperlink to a hidden sheet. The code used was as follows:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.ScreenUpdating = False
Worksheets("XYZ").Visible = xlSheetVisible
Sheets("XYZ").Visible = True
Sheets("XYZ").Select
Application.ScreenUpdating = True
End Sub

Having used the above I then added the following to hide all those sheets that were meant to be hidden using the following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.ScreenUpdating = False
Sheets("XYZ").Visible = True
Sheets("XYZ").Select
Range("A1").Select
Sheets("XYZ").Select
ActiveWindow.SelectedSheets.Visible = False
Range("A1").Select
Application.ScreenUpdating = True
End Sub
Any help would be appreciated. I've scoured the forum to no avail!!!

Thanks.

rbrhodes
08-28-2009, 06:08 PM
Hi sDave,

In the ThisWorkbook module, something like this to hide sheets on closing:


Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
'Do all
For Each sht In Sheets
'Hide based on name
If sht.Name <> "Sheet1" Then
sht.Visible = xlSheetHidden
End If
Next sht
'Cleanup
Set sht = Nothing

End Sub



In the Sheet that has the hyperlinks, this will extract the sheet name from the reference (eg: Sheet3!A1) and reveal the sheet:


Option Explicit
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim Excl As Long
Dim sht As String
Application.ScreenUpdating = False

'Get cutoff for sheet name
Excl = InStr(Target.Name, "!")
'Extract sheet name
sht = Left(Target.Name, Excl - 1)
'Reveal
Worksheets(sht).Visible = xlSheetVisible
'Go there
Sheets(sht).Activate

Application.ScreenUpdating = True
End Sub

SDave
09-01-2009, 03:45 PM
Thanks dr, the code works a treat. I have stumbled across one similiar but I couldn't get it to function the way it should have.

Much appreciated as always!!!

Thanks.