hide all sheets except main sheet

[Nikhil]

I have a main sheet and many other sheets. I want that when i open the
workbook, all the worksheets except the main sheet should be hidden.
My main sheets have hyperlinks to each of the worksheet. When the user
opens the work book, the user would see only the main sheet. The user
can then click on any of the icons that will have a hyperlink to the
reqd worksheet.

please let me know how to do this.

 

[Gary''s Student]

Put the following macro in the worksheet code area:

Private Sub Workbook_Open()
For Each w In Worksheets
If w.Name = "main" Then
Else
w.Visible = False
End If
Next
End Sub

 

[Nick Hodge]

Nikhil

This needs testing a little more but the first code goes in the ThisWorkbook
module to ensure all sheets are hidden when the workbook opens. The next
goes in the 'Main' worksheet module. The last needs to go in the worksheet
module for each sheet that is hidden/unhidden.

'Goes in This Workbook Module ensures all sheets are hidden on opening
Private Sub Workbook_Open()
Dim wks As Worksheet
On Error Resume Next
For Each wks In ThisWorkbook
If wks.Name <> "Main" Then 'Change "Main" to whatever it's called
wks.Visible = xlSheetVeryHidden
End If
Next wks
End Sub

'Goes in main sheet module and activates and unhides the sheet concerned
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim HAddress As String
Dim wksName As String
Dim wks As Worksheet
On Error GoTo reEnable
HAddress = Target.SubAddress
wksName = Left(HAddress, InStr(1, HAddress, "!") - 1)
Set wks = ThisWorkbook.Worksheets(wksName)
Application.EnableEvents = False
wks.Visible = xlSheetVisible
Target.Follow
Application.EnableEvents = True

reEnable:
Application.EnableEvents = True
End Sub

'Goes in each of the hidden sheet modules to hide them on deactivation
Private Sub Worksheet_Deactivate()
Me.Visible = xlSheetVeryHidden
End Sub


Remember, there is a lot of event code here, test, test and then when you've
done that... test again ;-)
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Nick Hodge]

Gary"s

Or maybe the 'ThisWorkbook' module ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Gary''s Student]

Thanks for the correction !!

 

[Nikhil]

Nikhil

This needs testing a little more but the first code goes in the ThisWorkbook
module to ensure all sheets are hidden when the workbook opens. The next
goes in the 'Main' worksheet module. The last needs to go in the worksheet
module for each sheet that is hidden/unhidden.

'Goes in This Workbook Module ensures all sheets are hidden on opening
Private Sub Workbook_Open()
Dim wks As Worksheet
On Error Resume Next
For Each wks In ThisWorkbook
If wks.Name <> "Main" Then 'Change "Main" to whatever it's called
wks.Visible = xlSheetVeryHidden
End If
Next wks
End Sub

'Goes in main sheet module and activates and unhides the sheet concerned
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim HAddress As String
Dim wksName As String
Dim wks As Worksheet
On Error GoTo reEnable
HAddress = Target.SubAddress
wksName = Left(HAddress, InStr(1, HAddress, "!") - 1)
Set wks = ThisWorkbook.Worksheets(wksName)
Application.EnableEvents = False
wks.Visible = xlSheetVisible
Target.Follow
Application.EnableEvents = True

reEnable:
Application.EnableEvents = True
End Sub

'Goes in each of the hidden sheet modules to hide them on deactivation
Private Sub Worksheet_Deactivate()
Me.Visible = xlSheetVeryHidden
End Sub

Remember, there is a lot of event code here, test, test and then when you've
done that... test again ;-)
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web:www.excelusergroup.org
web:www.nickhodge.co.uk







- Show quoted text -

Hi. Thanks.....But sorry if you find this a bit odd....

in the first place, please tell me how to put the code in the relevant
module..eg. i do not know how to open a work book module...leave
alone then to copy the code that you gave.

so once more, could you please help.

Also since i have excel 2007, i frequently get a msg...of macro being
disabled....plz advice for that as well....if its rqd for the present
task that we are working on.

Regards

 

[Nick Hodge]

Nikhil

Yes, you will get a warning that the file contains a macro, unless you set
the folder that contains the file as a trusted location via Office
Button>Excel options>Trust Center>Trust Center settings...>Trusted
locations.

To add the code, Press Alt+F11. In the window you will see in the 'Project
explorer' and entry for your file below which will be entries for all your
sheets and one called ThisWorkbook.

Right click on the 'ThisWorkbook' entry and select 'View code'. Paste the
first code in the window that pops up.

Right click the 'Main' sheet and select 'View code' and paste the second
code in there

Lastly, right click on each of the other worksheets in turn, selecting 'View
code' and paste the same third code in each sheet.

Close the VBE and save the file.

Same applies about testing as the code requires some knowledge of what is
going on.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Nikhil]

Hi. Thanks.....But sorry if you find this a bit odd....

in the first place, please tell me how to put the code in the relevant
module..eg. i do  not know how to open a work book module...leave
alone then to copy the code that you gave.

so once more, could you please help.

Also since i have excel 2007, i frequently get a msg...of macro being
disabled....plz advice for that as well....if its rqd for the present
task that we are working on.

Regards

Hi...am sorry for the delay...

well i did try to put in that code and run t..but somehow it does not
run and everytime i open the workbook, all the sheets are visible.
When i tried executing the code from VB (using the run button), i get
a message that macros are disabled. THis is inspite of the fact that i
have put the folder as a trusted location and have enabled macros to
be executed.

Plz help

Nikhil

 

[Robnific]

1. You download passwordx at www.allocator.nl
2. activate
3. open your workbook
4. use option Sesame Close in Passwordx : your workbook is saved and
all sheets but last open are hidden and closed
5. reopen workbook : last sheet on your screen opens.

Might work for your question
hth