Un-protect Multiple Worksheets

G

Guest

I have a large workbook that contains approx. 80 worksheets. I have each
sheet protected individually with the same password.
Can anyone tell me if there is a way to unprotect all sheets at one time so
that changes can be made quicker and easier.
I tend to spend more time un-protecting and re-protecting the sheets due to
the multitude of sheets.
Thanks
Ed
 
K

Ken Wright

Take your pick. All either courtesy of JE or based on his routine.

Public Sub ToggleProtect1()
'Courtesy of J E McGimpsey
'If only selected sheets are to be protected, then a toggle works well

Const PWORD As String = "ken"
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End If
End With
Next wkSht
MsgBox Mid(statStr, 2)
End Sub

-----------------------------------------

Sub Toggleprotect2()
'Courtesy of J E McGimpsey
'If only selected sheets are to be protected, then a toggle works well

Const PWORD As String = "ken"
Dim wkSht As Worksheet

For Each sh In ActiveWorkbook.Worksheets
If sh.ProtectContents = False Then
sh.Protect PWORD
Else
sh.Unprotect PWORD
End If
Next sh
End Sub

-----------------------------------------

Public Sub ProtectAllSheets()
'Space allowed for insertion of a password
'Code lists every sheet with it's protection status
Application.ScreenUpdating = False
Const PWORD As String = ""
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End With
Next wkSht
MsgBox Mid(statStr, 2)
Application.ScreenUpdating = True
End Sub

-----------------------------------------

Public Sub UnprotectAllSheets()
'Space allowed for insertion of a password
'Code lists every sheet with it's protection status
Application.ScreenUpdating = False
Const PWORD As String = ""
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
End With
Next wkSht
MsgBox Mid(statStr, 2)
Application.ScreenUpdating = True
End Sub
 
G

Guest

Thanks for your help Ken

Ken Wright said:
Take your pick. All either courtesy of JE or based on his routine.

Public Sub ToggleProtect1()
'Courtesy of J E McGimpsey
'If only selected sheets are to be protected, then a toggle works well

Const PWORD As String = "ken"
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End If
End With
Next wkSht
MsgBox Mid(statStr, 2)
End Sub

-----------------------------------------

Sub Toggleprotect2()
'Courtesy of J E McGimpsey
'If only selected sheets are to be protected, then a toggle works well

Const PWORD As String = "ken"
Dim wkSht As Worksheet

For Each sh In ActiveWorkbook.Worksheets
If sh.ProtectContents = False Then
sh.Protect PWORD
Else
sh.Unprotect PWORD
End If
Next sh
End Sub

-----------------------------------------

Public Sub ProtectAllSheets()
'Space allowed for insertion of a password
'Code lists every sheet with it's protection status
Application.ScreenUpdating = False
Const PWORD As String = ""
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End With
Next wkSht
MsgBox Mid(statStr, 2)
Application.ScreenUpdating = True
End Sub

-----------------------------------------

Public Sub UnprotectAllSheets()
'Space allowed for insertion of a password
'Code lists every sheet with it's protection status
Application.ScreenUpdating = False
Const PWORD As String = ""
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
End With
Next wkSht
MsgBox Mid(statStr, 2)
Application.ScreenUpdating = True
End Sub
Click to expand...
 
T

trussman

I use this to Unprotect

Sub UnpWorksheets()
Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets
sht.Unprotect PassWord:="record"
Next sht
End Sub

and this to protect

Sub PWorksheets()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
sht.Protect PassWord:="record"
sht.EnableSelection = xlNoRestrictions

Next sht
End Sub
 
T

trussman

I use this to Unprotect

Sub UnpWorksheets()
Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets
sht.Unprotect PassWord:="record"
Next sht
End Sub

and this to protect

Sub PWorksheets()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
sht.Protect PassWord:="record"
sht.EnableSelection = xlNoRestrictions

Next sht
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sheet protection complications 4
Protect multiple worksheets in a workbook at one time in EXCEL. 2
Multiple sheet unprotection not working 9
Excel 2007 bug caused by latest security patch KB973593 14
Simple workbook Protect 2
How to Store Passwords To Multiple Sheets and To Retain thatPasswords When the Workbook IS closed 1
Problems with Protect Method 8
Protect/Unprotecting Sheets with CmdButtons 10

Top