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" <ditchy@ncable.net.au> wrote in message
news:1114678021.698512.21550@l41g2000cwc.googlegroups.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" <ditchy@ncable.net.au> wrote in message
news:1114756825.169080.7390@f14g2000cwb.googlegroups.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" <ditchy@ncable.net.au> wrote in message
> news:1114756825.169080.7390@f14g2000cwb.googlegroups.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" <ditchy@ncable.net.au> wrote in message
news:1114775040.186024.264940@o13g2000cwo.googlegroups.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" <ditchy@ncable.net.au> wrote in message
> > news:1114756825.169080.7390@f14g2000cwb.googlegroups.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" <ditchy@ncable.net.au> wrote in message
news:1114779370.953133.210830@o13g2000cwo.googlegroups.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" <ditchy@ncable.net.au> wrote in message
> news:1114779370.953133.210830@o13g2000cwo.googlegroups.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
> >