Passowrd Protecting

I currently have 8 sheets within a workbook which are protected, on each
sheet I have some locked and unlocked cells. Each worksheet is for each day
of the week plus a totals sheet, therefore the layout for each sheet is
identical. At present If I need to make an amendment to any of the locked
cells I have to unprotect every sheet make the change and then proect every
sheet.

Is there anyway of proecting ALL the sheets at once and unprotecting them
all at once as this would reduce the time it takes.

Regards
The Rook

try a loop something like

for each ws in worksheets
If ws.Name <> "Sheet1" Then do your thing
Next ws
--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"TheRook" <TheRook@discussions.microsoft.com> wrote in message
news:3C70D48C-64A2-483C-8DF9-136479E6A12D@microsoft.com...
>I currently have 8 sheets within a workbook which are protected, on each
> sheet I have some locked and unlocked cells. Each worksheet is for each
> day
> of the week plus a totals sheet, therefore the layout for each sheet is
> identical. At present If I need to make an amendment to any of the locked
> cells I have to unprotect every sheet make the change and then proect
> every
> sheet.
>
> Is there anyway of proecting ALL the sheets at once and unprotecting them
> all at once as this would reduce the time it takes.
>
> Regards
> The Rook

You would need to use a VBA procedure:


Sub ProtectAll()
Dim SH As Object
For Each SH In ThisWorkbook.Sheets
SH.Protect
Next SH
End Sub

Sub UnProtectAll()
Dim SH As Object
For Each SH In ThisWorkbook.Sheets
SH.Unprotect
Next SH
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"TheRook" <TheRook@discussions.microsoft.com> wrote in message
news:3C70D48C-64A2-483C-8DF9-136479E6A12D@microsoft.com...
>I currently have 8 sheets within a workbook which are protected,
>on each
> sheet I have some locked and unlocked cells. Each worksheet is
> for each day
> of the week plus a totals sheet, therefore the layout for each
> sheet is
> identical. At present If I need to make an amendment to any of
> the locked
> cells I have to unprotect every sheet make the change and then
> proect every
> sheet.
>
> Is there anyway of proecting ALL the sheets at once and
> unprotecting them
> all at once as this would reduce the time it takes.
>
> Regards
> The Rook

Chip,

That worked a treat, but I forgot ti include that I want to use a password.

Can this be done?

"Chip Pearson" wrote:

> You would need to use a VBA procedure:
>
>
> Sub ProtectAll()
> Dim SH As Object
> For Each SH In ThisWorkbook.Sheets
> SH.Protect
> Next SH
> End Sub
>
> Sub UnProtectAll()
> Dim SH As Object
> For Each SH In ThisWorkbook.Sheets
> SH.Unprotect
> Next SH
> End Sub
>
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
> "TheRook" <TheRook@discussions.microsoft.com> wrote in message
> news:3C70D48C-64A2-483C-8DF9-136479E6A12D@microsoft.com...
> >I currently have 8 sheets within a workbook which are protected,
> >on each
> > sheet I have some locked and unlocked cells. Each worksheet is
> > for each day
> > of the week plus a totals sheet, therefore the layout for each
> > sheet is
> > identical. At present If I need to make an amendment to any of
> > the locked
> > cells I have to unprotect every sheet make the change and then
> > proect every
> > sheet.
> >
> > Is there anyway of proecting ALL the sheets at once and
> > unprotecting them
> > all at once as this would reduce the time it takes.
> >
> > Regards
> > The Rook
>
>
>

Use code like

Sub ProtectAll()
Dim SH As Object
For Each SH In ThisWorkbook.Sheets
SH.Protect Password:="the_password"
Next SH
End Sub

Sub UnProtectAll()
Dim SH As Object
For Each SH In ThisWorkbook.Sheets
SH.Unprotect Password:="the_password"
Next SH
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"TheRook" <TheRook@discussions.microsoft.com> wrote in message
news:D5963761-F160-48AA-834A-534DE02178DE@microsoft.com...
> Chip,
>
> That worked a treat, but I forgot ti include that I want to use
> a password.
>
> Can this be done?
>
> "Chip Pearson" wrote:
>
>> You would need to use a VBA procedure:
>>
>>
>> Sub ProtectAll()
>> Dim SH As Object
>> For Each SH In ThisWorkbook.Sheets
>> SH.Protect
>> Next SH
>> End Sub
>>
>> Sub UnProtectAll()
>> Dim SH As Object
>> For Each SH In ThisWorkbook.Sheets
>> SH.Unprotect
>> Next SH
>> End Sub
>>
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>> "TheRook" <TheRook@discussions.microsoft.com> wrote in message
>> news:3C70D48C-64A2-483C-8DF9-136479E6A12D@microsoft.com...
>> >I currently have 8 sheets within a workbook which are
>> >protected,
>> >on each
>> > sheet I have some locked and unlocked cells. Each worksheet
>> > is
>> > for each day
>> > of the week plus a totals sheet, therefore the layout for
>> > each
>> > sheet is
>> > identical. At present If I need to make an amendment to any
>> > of
>> > the locked
>> > cells I have to unprotect every sheet make the change and
>> > then
>> > proect every
>> > sheet.
>> >
>> > Is there anyway of proecting ALL the sheets at once and
>> > unprotecting them
>> > all at once as this would reduce the time it takes.
>> >
>> > Regards
>> > The Rook
>>
>>
>>