Protecting Sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nathan_savidge - 23 May 2008 11:34 GMT

Hi,

I have recently migrated data from one excel sheet to another. However it
is now required to protect all the migrated sheets.

I wish to do this via code rather than go into each sheet and protect.

I have opened each sheet with code and used the following code :

xl.ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingRows:=True

However, i can not put the password in, this just protects the sheet with no
password, is there a way of adding the password to the code?

Thanks

Nathan.

Reply to this Message

Mike H - 23 May 2008 12:17 GMT

Try

Sub marine()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="MyPass", DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingRows:=True

Next ws
End Sub

Mike

> Hi,
>
[quoted text clipped - 14 lines]
>
> Nathan.

Reply to this Message

Gord Dibben - 24 May 2008 01:12 GMT

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingRows:=True
Next n
Application.ScreenUpdating = True
End Sub

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Unprotect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub

Gord Dibben MS Excel MVP

>Hi,
>
[quoted text clipped - 14 lines]
>
>Nathan.

Reply to this Message