ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Unprotect sheets, how to? (https://www.excelbanter.com/excel-discussion-misc-queries/253015-unprotect-sheets-how.html)

fooreest
Unprotect sheets, how to?
 
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.

Dave Peterson
Unprotect sheets, how to?
 
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



fooreest wrote:

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.

--

Dave Peterson

fooreest
Unprotect sheets, how to?
 
Message apear:
"Something went wrong with R1,R2,R3....

when I run macro unprotect all

Thanks


"Dave Peterson" wrote in message
...
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



fooreest wrote:

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.

--

Dave Peterson


Dave Peterson
Unprotect sheets, how to?
 
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"


fooreest wrote:

Message apear:
"Something went wrong with R1,R2,R3....

when I run macro unprotect all

Thanks

"Dave Peterson" wrote in message
...
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



fooreest wrote:

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.

--

Dave Peterson

--

Dave Peterson

fooreest
Unprotect sheets, how to?
 
Sorry, sorry, Dave

Everything is OK, wrong pasword

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


"Dave Peterson" wrote in message
...
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



fooreest wrote:

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.

--

Dave Peterson


fooreest
Unprotect sheets, how to?
 
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






"Dave Peterson" wrote in message
...
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"


fooreest wrote:

Message apear:
"Something went wrong with R1,R2,R3....

when I run macro unprotect all

Thanks

"Dave Peterson" wrote in message
...
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



fooreest wrote:

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.

--

Dave Peterson

--

Dave Peterson


Gord Dibben
Unprotect sheets, how to?
 
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

On Mon, 11 Jan 2010 22:03:03 +0100, "fooreest" wrote:

Sorry, sorry, Dave

Everything is OK, wrong pasword

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


"Dave Peterson" wrote in message
...
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



fooreest wrote:

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.

--

Dave Peterson


John[_22_]
Unprotect sheets, how to?
 
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
"Dave Peterson" wrote in message
...
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"


fooreest wrote:

Message apear:
"Something went wrong with R1,R2,R3....

when I run macro unprotect all

Thanks

"Dave Peterson" wrote in message
...
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



fooreest wrote:

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.

--

Dave Peterson

--

Dave Peterson


fooreest
Unprotect sheets, how to?
 
You are right Gordon, problem is resolved with 2 lines from second Daves
post.
It works perfektly.



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Mon, 11 Jan 2010 22:03:03 +0100, "fooreest" wrote:

Sorry, sorry, Dave

Everything is OK, wrong pasword

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


"Dave Peterson" wrote in message
...
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



fooreest wrote:

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.

--

Dave Peterson

fooreest
Unprotect sheets, how to?
 
sorry, GORD

Gord Dibben
Unprotect sheets, how to?
 
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


On Mon, 11 Jan 2010 16:50:46 -0500, "John" wrote:

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
"Dave Peterson" wrote in message
...
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"


fooreest wrote:

Message apear:
"Something went wrong with R1,R2,R3....

when I run macro unprotect all

Thanks

"Dave Peterson" wrote in message
...
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



fooreest wrote:

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.

--

Dave Peterson

--

Dave Peterson


John[_22_]
Unprotect sheets, how to?
 
Hi Gord
It's working fine.
Thank you
Regards
John
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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


On Mon, 11 Jan 2010 16:50:46 -0500, "John" wrote:

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
"Dave Peterson" wrote in message
...
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"


fooreest wrote:

Message apear:
"Something went wrong with R1,R2,R3....

when I run macro unprotect all

Thanks

"Dave Peterson" wrote in message
...
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



fooreest wrote:

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.

--

Dave Peterson

--

Dave Peterson


All times are GMT +1. The time now is 06:07 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com