protecting multiple worksheets
 

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.

Sue Wrote:
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.

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 Sub


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=466737

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

"Paul Sheppard" wrote:


Sue Wrote:
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.

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 Sub


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=466737