Protecting & Unprotecting Sheets

I have a Workbook that contains 6 seperate Sheets. On the PC using this
workbook, there can be 3 other workbooks open at the same time. To cut
corners, I use the Sub below to remove protection from all Sheets in
the Workbook that contains the 6 seperate sheets, and the following Sub
to turn it back on, can you tell me if this is the correct method, or
is there a better way. For some reason, I keep getting problems when
trying to enter data i unprotected Cells, and was wondering if I am
doing this wrong.

Thanks

Pete


Private Sub ProtectionOff()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Unprotect ("PassWord")
Next ws
End Sub


Private Sub ProtectionOn()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect Password:="PassWord"
Next ws
End Sub

I use the following and have nver had any problems

Sub ProtectSheets()
Dim mySheet As Worksheet
For Each mySheet In Worksheets
mySheet.Select
mySheet.Protect "password", True, True, True
Next mySheet
End Sub
Sub UnprotectSheets()
Dim mySheet As Worksheet
For Each mySheet In Worksheets
mySheet.Select
mySheet.Unprotect "password"
Next mySheet
End Sub

Hope this helps

Nick.

That is exactly what I use on a 63 sheet workbook. I have assigned keyboard
shortcuts (Ctrl+u, Ctrl+p) to the macros for ease of use. This book has been
distributed across my company for 3 years with no problems.
Mike F
"Pete" <psowerby@murton-view.freeserve.co.uk> wrote in message
news:1115715996.188995.131850@o13g2000cwo.googlegroups.com...
>I have a Workbook that contains 6 seperate Sheets. On the PC using this
> workbook, there can be 3 other workbooks open at the same time. To cut
> corners, I use the Sub below to remove protection from all Sheets in
> the Workbook that contains the 6 seperate sheets, and the following Sub
> to turn it back on, can you tell me if this is the correct method, or
> is there a better way. For some reason, I keep getting problems when
> trying to enter data i unprotected Cells, and was wondering if I am
> doing this wrong.
>
> Thanks
>
> Pete
>
>
> Private Sub ProtectionOff()
> Dim ws As Worksheet
> For Each ws In Worksheets
> ws.Unprotect ("PassWord")
> Next ws
> End Sub
>
>
> Private Sub ProtectionOn()
> Dim ws As Worksheet
> For Each ws In Worksheets
> ws.Protect Password:="PassWord"
> Next ws
> End Sub
>

Hi

For some reason one of the Cells in a Protected Sheet was "unmerged",
which through out some calculations relying on that cell, any ideas how
this can happen on a Protected Sheet.

The operator is very inexperienced with Excel, so may have
inadvertently done something to unmerge the cells, although I have
tried, I cannot achieve this.

Pete

If it just happened once I would consider it a glitch. You shouldn't be able
to unmerge on a protected sheet. If it repeatedly happens with this user, I
would troubleshoot the user.
Mike F
"Pete" <psowerby@murton-view.freeserve.co.uk> wrote in message
news:1115725033.489393.275860@o13g2000cwo.googlegroups.com...
> Hi
>
> For some reason one of the Cells in a Protected Sheet was "unmerged",
> which through out some calculations relying on that cell, any ideas how
> this can happen on a Protected Sheet.
>
> The operator is very inexperienced with Excel, so may have
> inadvertently done something to unmerge the cells, although I have
> tried, I cannot achieve this.
>
> Pete
>

thanks everone, sorted now.

Pete

Your macro works on the activeworkbook. If your 6 sheet workbook isn't the
activeworkbook, then it will not have it's sheets unprotected. You can do
(adjust both macros)

Private Sub ProtectionOff()
Dim ws As Worksheet
For Each ws In Workbooks("Mybook.xls").Worksheets
ws.Unprotect ("PassWord")
Next ws
End Sub

to specify exactly which workbook.

--
Regards,
Tom Ogilvy


"Pete" <psowerby@murton-view.freeserve.co.uk> wrote in message
news:1115715996.188995.131850@o13g2000cwo.googlegroups.com...
> I have a Workbook that contains 6 seperate Sheets. On the PC using this
> workbook, there can be 3 other workbooks open at the same time. To cut
> corners, I use the Sub below to remove protection from all Sheets in
> the Workbook that contains the 6 seperate sheets, and the following Sub
> to turn it back on, can you tell me if this is the correct method, or
> is there a better way. For some reason, I keep getting problems when
> trying to enter data i unprotected Cells, and was wondering if I am
> doing this wrong.
>
> Thanks
>
> Pete
>
>
> Private Sub ProtectionOff()
> Dim ws As Worksheet
> For Each ws In Worksheets
> ws.Unprotect ("PassWord")
> Next ws
> End Sub
>
>
> Private Sub ProtectionOn()
> Dim ws As Worksheet
> For Each ws In Worksheets
> ws.Protect Password:="PassWord"
> Next ws
> End Sub
>