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.
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.