Protecting & Unprotecting Sheets

[Pete]

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

 

[Mike Fogleman]

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]

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

 

[Tom Ogilvy]

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.

 

[Mike Fogleman]

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]

thanks everone, sorted now.

Pete