Protect all sheets at once

[~me~]

I have a workbook with about 30 pages. I want to be able to protect and
unprotect them all at once. I cannot select all sheets and then protect them
because the option will not be available. Any Ideas?
TIA
Wayne

[davegb]

~me~ wrote:
> I have a workbook with about 30 pages. I want to be able to protect and
> unprotect them all at once. I cannot select all sheets and then protect them
> because the option will not be available. Any Ideas?
> TIA
> Wayne

I have a couple of macros I use to protect/unprotect each worksheet in
large workbooks. I have them assigned to buttons on a custom toolbar I
use when developing worksheets. If you'd like me to post the macros,
I'd be happy to. If you don't know how to implement them, I can
explain.

[Paul B]

Wayne,

Sub Protect_All_Sheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Protect password:="123"

Next ws

End Sub



Sub Unprotect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect password:="123"
Next ws
End Sub
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003


"~me~" <[email protected]> wrote in message
news:[email protected]...
>I have a workbook with about 30 pages. I want to be able to protect and
> unprotect them all at once. I cannot select all sheets and then protect
> them
> because the option will not be available. Any Ideas?
> TIA
> Wayne
>
>

[~me~]

Paul,
Thank you very much, that was perfect, I just altered it a tad to allow for
formatting and took out the password.
Thank you I appreciate your help
Wayne

"Paul B" <[email protected]> wrote in message
news:#[email protected]...
> Wayne,
>
> Sub Protect_All_Sheets()
>
> Dim ws As Worksheet
>
> For Each ws In ThisWorkbook.Worksheets
>
> ws.Protect password:="123"
>
> Next ws
>
> End Sub
>
>
>
> Sub Unprotect_All_Sheets()
> Dim ws As Worksheet
> For Each ws In ThisWorkbook.Worksheets
> ws.Unprotect password:="123"
> Next ws
> End Sub
> --
> Paul B
> Always backup your data before trying something new
> Please post any response to the newsgroups so others can benefit from it
> Feedback on answers is always appreciated!
> Using Excel 2002 & 2003
>
>
> "~me~" <[email protected]> wrote in message
> news:[email protected]...
> >I have a workbook with about 30 pages. I want to be able to protect and
> > unprotect them all at once. I cannot select all sheets and then protect
> > them
> > because the option will not be available. Any Ideas?
> > TIA
> > Wayne
> >
> >
>
>

[Paul B]

Your welcome

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"~me~" <[email protected]> wrote in message
news:[email protected]...
> Paul,
> Thank you very much, that was perfect, I just altered it a tad to allow
for
> formatting and took out the password.
> Thank you I appreciate your help
> Wayne
>
> "Paul B" <[email protected]> wrote in message
> news:#[email protected]...
> > Wayne,
> >
> > Sub Protect_All_Sheets()
> >
> > Dim ws As Worksheet
> >
> > For Each ws In ThisWorkbook.Worksheets
> >
> > ws.Protect password:="123"
> >
> > Next ws
> >
> > End Sub
> >
> >
> >
> > Sub Unprotect_All_Sheets()
> > Dim ws As Worksheet
> > For Each ws In ThisWorkbook.Worksheets
> > ws.Unprotect password:="123"
> > Next ws
> > End Sub
> > --
> > Paul B
> > Always backup your data before trying something new
> > Please post any response to the newsgroups so others can benefit from it
> > Feedback on answers is always appreciated!
> > Using Excel 2002 & 2003
> >
> >
> > "~me~" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have a workbook with about 30 pages. I want to be able to protect
and
> > > unprotect them all at once. I cannot select all sheets and then
protect
> > > them
> > > because the option will not be available. Any Ideas?
> > > TIA
> > > Wayne
> > >
> > >
> >
> >
>
>

[EdMac]

The other alternative is to download ASAP utilities which provides this as one of the many additional add-ins.

Ed