Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unprotect sheets, how to?
sorry, GORD
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does Excel unprotect all my sheets when I hit save? | Excel Discussion (Misc queries) | |||
Protect/unprotect all sheets at once? | New Users to Excel | |||
Protect-Unprotect all the sheets | Excel Worksheet Functions | |||
unprotect sheets | Excel Worksheet Functions | |||
Unprotect Sheets | Excel Worksheet Functions |