Protecting Sheet

[nathan_savidge]

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.

 

[Mike H]

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

 

[Gord Dibben]

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