Unprotect sheets, how to?

  • Thread starter Thread starter fooreest
  • Start date Start date
F

fooreest

I have sheets named R1,R2,R3, ...... R30
All 30 are with same pasword protected

How to unprotect all 30 sheets at same time? One by one is so hard.
Thanks a lot.
 
You could use a macro...

Option Explicit
Sub UnprotectAll()
Dim wks As Object
Dim wCtr As Long

For wCtr = 1 To 30
Set wks = Sheets("R" & wCtr)
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
On Error Resume Next
.Unprotect
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 wCtr

End Sub
Sub ProtectAll()
Dim wks As Object
Dim wCtr As Long

For wCtr = 1 To 30
Set wks = Sheets("R" & wCtr)
With wks
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
'do nothing, it's already protected
Else
On Error Resume Next
.Protect
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 wCtr
End Sub
 
You'll have to supply the password.

Change this line:
..Unprotect
to:
..Unprotect password:="whateveryourpasswordishere"

And similarly in the other procedure, too:
..Protect password:="whateveryourpasswordishere"
 
Sorry, sorry, Dave

Everything is OK, wrong pasword

(this is faster way, but 30-times is neded pasword reenter to box)
 
Thanks a lot Dave

Macro for protect and unprotect works perfektly
Your second message (with two lines) is exactly what I neded


Greetings
from Croatia, Dubravko
 
You've got something wrong in that case.

The sheets should all unprotect without entering a password.

The code takes care of the password.


Gord Dibben MS Excel MVP
 
Hi Dave
Sorry to cut in someone else posting but I like your code.
What would I need to change if my sheet name are all different.
I've got 5 sheets.
Regards
John
 
You are right Gordon, problem is resolved with 2 lines from second Daves
post.
It works perfektly.
 
With only 5 sheets you can use

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Protect Password:="justme"
Next N
Application.ScreenUpdating = True
End Sub

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Unprotect Password:="justme"
Next N
Application.ScreenUpdating = True
End Sub

Add any protection properties if you choose.

for example...........

..Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowInsertingColumns:=True, AllowInsertingRows:=True
.EnableSelection = xlNoRestrictions


Gord Dibben MS Excel MVP


Hi Dave
Sorry to cut in someone else posting but I like your code.
What would I need to change if my sheet name are all different.
I've got 5 sheets.
Regards
John
Click to expand...
 
Hi Gord
It's working fine.
Thank you
Regards
John
Gord Dibben said:
With only 5 sheets you can use

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Protect Password:="justme"
Next N
Application.ScreenUpdating = True
End Sub

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Unprotect Password:="justme"
Next N
Application.ScreenUpdating = True
End Sub

Add any protection properties if you choose.

for example...........

.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowInsertingColumns:=True, AllowInsertingRows:=True
.EnableSelection = xlNoRestrictions


Gord Dibben MS Excel MVP
Click to expand...
 
Back
Top