Is there a way of protecting multiple sheets at once? I have a workbook with
100 worksheets and i want to protect them all, but dont want to type the
password 200 times. Using Excel 2007

TIA

Esra

You could use a macro and if you use the same password for all the sheets, it
would look something like:

Option Explicit
Sub UnprotectAll()
Dim wks As Worksheet
Dim pwd As String
pwd = InputBox(Prompt:="What's the password, Kenny?")

If Trim(pwd) = "" Then
Exit Sub
End If

For Each wks In ActiveWorkbook.Worksheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
On Error Resume Next
.Unprotect Password:=pwd
If Err.Number < 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks

End Sub
Sub ProtectAll()
Dim wks As Worksheet
Dim pwd As String
pwd = InputBox(Prompt:="What's the password, Kenny?")

If Trim(pwd) = "" Then
Exit Sub
End If

For Each wks In ActiveWorkbook.Worksheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
'do nothing, it's already protected
Else
On Error Resume Next
.Protect Password:=pwd
If Err.Number < 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks
End Sub

Then back to excel and save this workbook with a nice name.

Anytime you want to unprotect or protect all the worksheets in any workbook, you
can open this file.

Then activate the workbook that you want to make changes to.
Hit alt-f8
Select the macro
and click Run

If you really wanted, you could embed the password directly in the code (both
procedures) and not be bothered with a prompt.

Change this line:

pwd = InputBox(Prompt:="What's the password, Kenny?")
to
pwd = "TopSecretPaSsWord1234_x"


If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)


Esradekan wrote:

Is there a way of protecting multiple sheets at once? I have a workbook with
100 worksheets and i want to protect them all, but dont want to type the
password 200 times. Using Excel 2007

TIA

Esra

--

Dave Peterson

Perfect, thanks so much. And so very quickly too.

Esra


"Dave Peterson" wrote:

You could use a macro and if you use the same password for all the sheets, it
would look something like:

Option Explicit
Sub UnprotectAll()
Dim wks As Worksheet
Dim pwd As String
pwd = InputBox(Prompt:="What's the password, Kenny?")

If Trim(pwd) = "" Then
Exit Sub
End If

For Each wks In ActiveWorkbook.Worksheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
On Error Resume Next
.Unprotect Password:=pwd
If Err.Number < 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks

End Sub
Sub ProtectAll()
Dim wks As Worksheet
Dim pwd As String
pwd = InputBox(Prompt:="What's the password, Kenny?")

If Trim(pwd) = "" Then
Exit Sub
End If

For Each wks In ActiveWorkbook.Worksheets
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
'do nothing, it's already protected
Else
On Error Resume Next
.Protect Password:=pwd
If Err.Number < 0 Then
MsgBox "Something went wrong with: " & wks.Name
Err.Clear
'exit for 'stop trying???
End If
On Error GoTo 0
End If
End With
Next wks
End Sub

Then back to excel and save this workbook with a nice name.

Anytime you want to unprotect or protect all the worksheets in any workbook, you
can open this file.

Then activate the workbook that you want to make changes to.
Hit alt-f8
Select the macro
and click Run

If you really wanted, you could embed the password directly in the code (both
procedures) and not be bothered with a prompt.

Change this line:

pwd = InputBox(Prompt:="What's the password, Kenny?")
to
pwd = "TopSecretPaSsWord1234_x"


If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)


Esradekan wrote:

Is there a way of protecting multiple sheets at once? I have a workbook with
100 worksheets and i want to protect them all, but dont want to type the
password 200 times. Using Excel 2007

TIA

Esra

--

Dave Peterson