Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
protecting and unprotecting more than 1 worksheets
is there a code to protect more than 1 worksheets using vba code using
one password? is there also a code to unprotect it as well? thanks much. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
protecting and unprotecting more than 1 worksheets
generally you need to do it a sheet at a time.
-- Regards, Tom Ogilvy "Jeffrey" wrote: is there a code to protect more than 1 worksheets using vba code using one password? is there also a code to unprotect it as well? thanks much. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
protecting and unprotecting more than 1 worksheets
I saved this from a previous post:
Maybe you can create a top secret macro that unprotects all the worksheets in the activeworkbook and then another macro that protects all those sheets. Start a new workbook hit alt-f11 (to get to the VBE where macros live) Hit F4 (to see the project explorer--kind of like windows explorer) select your project Insert|Module Paste this into the newly opened code window: 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 these lines: pwd = InputBox(Prompt:="What's the password, Kenny?") to pwd = "TopSecretPaSsWord1234_x" Jeffrey wrote: is there a code to protect more than 1 worksheets using vba code using one password? is there also a code to unprotect it as well? thanks much. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting and Unprotecting several worksheets at one time | Excel Discussion (Misc queries) | |||
Protecting and Unprotecting more than 1 worksheets | Excel Programming | |||
Macro for protecting and unprotecting multiple worksheets | Excel Discussion (Misc queries) | |||
Password Protecting/Unprotecting Worksheets using VBA code? | Excel Programming | |||
Protecting & unprotecting worksheets | Excel Programming |