vba 如何通过单击保护 Excel 工作簿中的所有工作表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/191400/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I protect all worksheet in an Excel workbook with a single click?
提问by Dheer
I have around 25 worksheets in my workbook (Excel spreadsheet). Is there a way I can protect all the 25 worksheets in single click ? or this feature is not available and I will have to write a VBA code to accomplish this. I need very often to protect all sheets and unprotect all sheets and doing individually is time consuming
我的工作簿（Excel 电子表格）中有大约 25 个工作表。有没有办法可以通过单击保护所有 25 个工作表？或者此功能不可用，我将不得不编写 VBA 代码来完成此操作。我经常需要保护所有工作表并取消保护所有工作表并且单独进行非常耗时
回答by Ben Hoffstein
I don't believe there's a way to do it without using VBA. If you are interested in a VBA solution, here is the code:
我不相信有一种方法可以不使用 VBA 来做到这一点。如果您对 VBA 解决方案感兴趣，这里是代码：
Dim ws as Worksheet
Dim pwd as String
pwd = "" ' Put your password here
For Each ws In Worksheets
ws.Protect Password:=pwd
Next ws
Unprotecting is virtually the same:
取消保护实际上是一样的：
Dim ws as Worksheet
Dim pwd as String
pwd = "" ' Put your password here
For Each ws In Worksheets
ws.Unprotect Password:=pwd
Next ws
回答by ChrisB
You can protect all worksheets from user changes but still allow VBA scripts to make changes with the "UserInterfaceOnly" option. This workaround lets you run any VBA script on the worksheets without having to protect and unprotect each time:
您可以保护所有工作表免受用户更改，但仍允许 VBA 脚本使用“UserInterfaceOnly”选项进行更改。此解决方法可让您在工作表上运行任何 VBA 脚本，而无需每次都进行保护和取消保护：
Dim ws as Worksheet
Dim pwd as String
pwd = "" ' Put your password here
or Each ws In Worksheets
ws.Protect Password:=pwd, UserInterfaceOnly:=True
Next ws
Unprotecting is the same as the solution offered by Ben Hoffstein:
取消保护与 Ben Hoffstein 提供的解决方案相同：
Dim ws as Worksheet
Dim pwd as String
pwd = "" ' Put your password here
For Each ws In Worksheets
ws.Unprotect Password:=pwd
Next ws
You can access this macro with a button/shortcut. In Excel 2010 you right-click on the Quick Access toolbar and select "Customize Quick Access Toolbar". In the drop down menu to choose commands, select "Macros". Then click the VBA script you created to protect (or unprotect). Finally click the "Add > >" button and then "OK" to save it.
您可以使用按钮/快捷方式访问此宏。在 Excel 2010 中，右键单击快速访问工具栏并选择“自定义快速访问工具栏”。在下拉菜单中选择命令，选择“宏”。然后单击您创建的要保护（或取消保护）的 VBA 脚本。最后单击“添加 > >”按钮，然后单击“确定”保存它。
回答by Steven Robbins
Don't think there's a button to do it, but it's simple enough code:
不要认为有一个按钮可以做到这一点，但它的代码足够简单：
For Each protSheet In Worksheets protSheet.Protect Password := "boo" Next protSheet
对于工作表中的每个 protSheet protSheet.Protect 密码 := "boo" 下一个 protSheet
回答by Steven Robbins
You can protect the workbook rather than each sheet and this will stop changes being made across the entire workbook
您可以保护工作簿而不是每个工作表，这将停止对整个工作簿进行的更改
