macro to hide sheets

[ditchy]

Hello there,
would anyone be able to help me with this please.
I have a workbook with 100+ sheets and I need to hide all but two
until a password is entered, then the rest of the sheets can be viewed.
All help is appreciated
Thanks, Ditchy

 

[Bob Phillips]

The password routine is simple and not robuts, but may suit your purpose

Sub HideSheets
For Each sh In Activeworkbook.Worksheets
If sh.Name <> "some name" And sh.Name <> "some other name" Then
sh.Visible = xlSheetHidden
End If
Next sh
End Sub

Sub ShowSheets
Dim sPass
sPass = Inputbox ("Supply password")
If sPass = "abc" Then
For Each sh In Activeworkbook.Worksheets
sh.Visible = xlSheetHidden
Next sh
End If
End Sub

 

[ditchy]

Thanks for that info Bob,
what I really need is when all the sheets are hidden except for the (3)
I wan't left on view, they can be viewed. If there is a password given
the rest of the hidden sheets become visable.
thanks again
Ditchy

 

[Bob Phillips]

Isn't that what I gave?

 

[ditchy]

Hi there Bob
looking at your answer again yes it is, my problem is I have over 100
sheets and I was hoping not to have to name them all for your macro, is
there another way around it. Say name the 3 I want on view, hide the
rest and to access the hidden only by password. If no password given
still be able to veiw/edit the original 3 on view.
Thank you for your patience
regards
Ditchy

 

[Bob Phillips]

I still think it is doing what you want, albeit with 2 names not 3.

It will hide all but those two, and will unhide hidden sheets when a
password is given.

 

[ditchy]

Hi Bob,
tried it again but it comes up with an error (unable to set the
visable property of the worksheet class) (run-time error 1004), and
also hides an extra sheet when password is entered.
The sheets can still be accessed by Format /sheet /unhide,hide.
not sure what to do from here, suggestions?
regards
Ditchy

 

[Bob Phillips]

Try this


Sub HideSheets()
For Each sh In Activeworkbook.Worksheets
If sh.Name <> "some name" And sh.Name <> "some other name" Then
sh.Visible = xlSheetVeryHidden
End If
Next sh
End Sub

Sub ShowSheets()
Dim sPass
sPass = Inputbox ("Supply password")
If sPass = "abc" Then
For Each sh In Activeworkbook.Worksheets
sh.Visible = xlSheetVisible
Next sh
End If
End Sub

 

[ditchy]

Thank you Bob
will give this a try
your help is much appreciated
regards
ditchy