Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I get create one password for multiple tabs/worksheets?
Hi, Please help! I am having to protect and unprotect multiple worksheets
and I would just like to unprotect and protect once with one password. Is that possible? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I get create one password for multiple tabs/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 this line: pwd = InputBox(Prompt:="What's the password, Kenny?") to pwd = "TopSecretPaSsWord1234_x" If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) quickkaren wrote: Hi, Please help! I am having to protect and unprotect multiple worksheets and I would just like to unprotect and protect once with one password. Is that possible? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create multiple sheet tabs from multiple cells. | Excel Worksheet Functions | |||
password protect multiple worksheets in excel | Excel Discussion (Misc queries) | |||
Cell Password Protection-Multiple Worksheets | Excel Worksheet Functions | |||
Add multiple excel worksheets(tabs) with jpgs | Excel Worksheet Functions | |||
How can I create multiple rows of worksheet tabs in a workbook? | Excel Discussion (Misc queries) |