protecting multiple worksheets

S

Sue

Is there any way of protecting worksheets in an excel 2003 workbook all at
once instead of having to do each individual worksheet? I have 17 worksheets
in two workbooks so individually it takes alot of time to password them. Any
ideas.
 
P

Paul Sheppard

Sue said:
Is there any way of protecting worksheets in an excel 2003 workbook al
at
once instead of having to do each individual worksheet? I have 1
worksheets
in two workbooks so individually it takes alot of time to passwor
them. Any
ideas.
Click to expand...

Hi Sue

The best way would be with a macro

Try this >

Public Sub Protect_All()
Dim wks As Worksheet
Dim vPword As Variant
vPword = Application.InputBox( _
prompt:="Enter Password: ", _
Title:="Protect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
For Each wks In ActiveWorkbook.Worksheets
wks.Protect vPword
Next
End Sub

and to Unprotect try this >

Public Sub Unprotect_All()
Dim wks As Worksheet
Dim vPword As Variant
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect vPword
Do While .ProtectContents
vPword = Application.InputBox( _
prompt:="Enter password for " & .Name, _
Title:="Unprotect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
.Unprotect vPword
Loop
End With
Next
End Su
 
S

Sue

Hi Paul,
I haven't done a macro but funnily enough I have started reading how to do
them last night off the getting started site by d m c ritchie, so I will give
it a go.
Thanks heaps
Sue
 
Top