Excel Macros to Protect / Unprotect Multiple Sheets 1

[ALKnapp]

Hi, I'm relatively new to Macros and I'm running into a snag. Your help is appreciated!!

I have an Excel File with multiple sheets. Each sheet contains macros that copy / paste. I want to password protect each sheet. Because the macro copies and pastes, I needed to add code to the macro that unprotects and re-protects the sheet. This worked great for one sheet as long as the other sheets were unprotected. As soon as I protect the other sheets, I receive errors ("Unable to set the IncludeNumber property of the style class). I have included the Macro code for one of my Macros.

Does anyone have any suggestions for how I can get this to work? Thanks for the help!!

Sub PrintMachineryList()
Application.Run "Zoom60Percent"
Range("A1&quot.Select
Range("A1:E57&quot.Select
ActiveSheet.Unprotect ("xyz&quot
With ActiveWorkbook.Styles("Normal&quot
.IncludeNumber = False
.IncludeFont = False
.IncludeAlignment = False
.IncludeBorder = False
.IncludePatterns = True
.IncludeProtection = False
End With
Selection.Style = "Normal"
Selection.PrintOut Copies:=1, Collate:=True
Range("A100:E156&quot.Select
Selection.Copy
Range("A1:E57&quot.Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Protect ("xyz&quot, DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("C3&quot.Select
End Sub

 

[Pcfred]

if you don't mind the password being the same for all sheets you could use something like this.

'to protect
For i = 1 To ThisWorkbook.Worksheets.Count

Worksheets(i).Protect Password:="mypassword"

Next i
i = 1

to unprotect

For i = 1 To ThisWorkbook.Worksheets.Count

Worksheets(i).UnProtect Password:="mypassword"

Next i
i = 1

 

[xlbo]

An easier way round this may be to use:
For each ws in thisworkbook.worksheets
ws.protect userinterfaceonly:= true
next

in the workbook open event

This will mean that from a user's perspective, the worksheets are protected but macros can run without having to protect and unprotect. This must be set in the workbook open event as it needs to be reset every time the workbook is opened
ps Credit for this info goes to Mike smith (cheers Mike - saved me a whole bunch of work with that one) Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde