macro to hide sheets
 

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

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

--
HTH

Bob Phillips

"ditchy" wrote in message
ups.com...
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

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

Isn't that what I gave?

--
HTH

Bob Phillips

"ditchy" wrote in message
ps.com...
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

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 wrote:
Isn't that what I gave?

--
HTH

Bob Phillips


"ditchy" wrote in message
ps.com...
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

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.

--
HTH

Bob Phillips

"ditchy" wrote in message
oups.com...
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 wrote:
Isn't that what I gave?

--
HTH

Bob Phillips


"ditchy" wrote in message
ps.com...
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

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

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



--
HTH

Bob Phillips

"ditchy" wrote in message
oups.com...
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

Thank you Bob
will give this a try
your help is much appreciated
regards
ditchy
Bob Phillips wrote:
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



--
HTH

Bob Phillips

"ditchy" wrote in message
oups.com...
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