Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Worksheets - Protection
What is the most best way, with a macro, to Protect all worksheets in a
workbook using a popup asking for a password, and specify what functions a users can use? And then to unlock it also, with a popup asking for the password. For example, I want to protect all the worksheets and have users be able to "Select Unlocked Cells", "Select Locked Cells", and "Format Cells". Also, to unprotect all the worksheets with a macro that pops up asking for the password to unprotect all the sheets. I hope that makes sense. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Worksheets - Protection
This should be close...
Sub ProtectAll() Dim wks As Worksheet Dim strPassword strPassword = InputBox("Enter the password", "Password") For Each wks In Worksheets wks.EnableSelection = xlNoRestrictions wks.Protect Password:=strPassword, _ Contents:=True, _ AllowFormattingCells:=True Next wks End Sub Sub UnProtectAll() Dim wks As Worksheet Dim strPassword strPassword = InputBox("Enter the password", "Password") On Error Resume Next For Each wks In Worksheets wks.Unprotect Password:=strPassword Next wks On Error GoTo 0 End Sub -- HTH... Jim Thomlinson "Josh O." wrote: What is the most best way, with a macro, to Protect all worksheets in a workbook using a popup asking for a password, and specify what functions a users can use? And then to unlock it also, with a popup asking for the password. For example, I want to protect all the worksheets and have users be able to "Select Unlocked Cells", "Select Locked Cells", and "Format Cells". Also, to unprotect all the worksheets with a macro that pops up asking for the password to unprotect all the sheets. I hope that makes sense. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Worksheets - Protection
Thanks Jim.
"Jim Thomlinson" wrote: This should be close... Sub ProtectAll() Dim wks As Worksheet Dim strPassword strPassword = InputBox("Enter the password", "Password") For Each wks In Worksheets wks.EnableSelection = xlNoRestrictions wks.Protect Password:=strPassword, _ Contents:=True, _ AllowFormattingCells:=True Next wks End Sub Sub UnProtectAll() Dim wks As Worksheet Dim strPassword strPassword = InputBox("Enter the password", "Password") On Error Resume Next For Each wks In Worksheets wks.Unprotect Password:=strPassword Next wks On Error GoTo 0 End Sub -- HTH... Jim Thomlinson "Josh O." wrote: What is the most best way, with a macro, to Protect all worksheets in a workbook using a popup asking for a password, and specify what functions a users can use? And then to unlock it also, with a popup asking for the password. For example, I want to protect all the worksheets and have users be able to "Select Unlocked Cells", "Select Locked Cells", and "Format Cells". Also, to unprotect all the worksheets with a macro that pops up asking for the password to unprotect all the sheets. I hope that makes sense. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Worksheets - Protection
Here is a link to some code to remove passwords for the case when someone can
not rememeber the password that they added... http://www.mcgimpsey.com/excel/removepwords.html -- HTH... Jim Thomlinson "Josh O." wrote: Thanks Jim. "Jim Thomlinson" wrote: This should be close... Sub ProtectAll() Dim wks As Worksheet Dim strPassword strPassword = InputBox("Enter the password", "Password") For Each wks In Worksheets wks.EnableSelection = xlNoRestrictions wks.Protect Password:=strPassword, _ Contents:=True, _ AllowFormattingCells:=True Next wks End Sub Sub UnProtectAll() Dim wks As Worksheet Dim strPassword strPassword = InputBox("Enter the password", "Password") On Error Resume Next For Each wks In Worksheets wks.Unprotect Password:=strPassword Next wks On Error GoTo 0 End Sub -- HTH... Jim Thomlinson "Josh O." wrote: What is the most best way, with a macro, to Protect all worksheets in a workbook using a popup asking for a password, and specify what functions a users can use? And then to unlock it also, with a popup asking for the password. For example, I want to protect all the worksheets and have users be able to "Select Unlocked Cells", "Select Locked Cells", and "Format Cells". Also, to unprotect all the worksheets with a macro that pops up asking for the password to unprotect all the sheets. I hope that makes sense. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protection on Worksheets | Excel Discussion (Misc queries) | |||
Protection of worksheets | Charts and Charting in Excel | |||
Protection Properties on WorkSheets | Excel Programming | |||
Protection of identical cells on multiple worksheets | Excel Worksheet Functions | |||
Cell Password Protection-Multiple Worksheets | Excel Worksheet Functions |