vba 如何在打开 Excel 工作簿时运行宏，然后在运行完成后重新保护？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19760838/
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 you run macros upon opening Excel workbook, then re-protect after finished running?
提问by crazian
Is there a way to first, unprotect a workbook at the beginning of opening, run all the workbook_open()macros and then re-protect the workbook so that a common user can not change anything on any worksheets? I'd like to prevent the common user from changing anything but allow the macros to do its auto-update and computations.
有没有办法首先在打开开始时取消保护工作簿，运行所有workbook_open()宏然后重新保护工作簿，以便普通用户无法更改任何工作表上的任何内容？我想阻止普通用户更改任何内容，但允许宏进行自动更新和计算。
Any suggestions? Is this even feasible?
有什么建议？这甚至可行吗？
回答by ARich
What I think you're looking for is the Worksheet.Protectmethod.
我认为你正在寻找的是Worksheet.Protect方法。
At the beginning of your Workbook_Openevent, place this code:
在Workbook_Open活动开始时，放置以下代码：
ThisWorkbook.Sheets("SheetYouWantToUnprotect").Unprotect _ Password:="TheSheetPassword"
Then at the end of your Workbook_Openevent code, you want to protect the sheet again, so place this code:
然后在Workbook_Open事件代码的末尾，您想再次保护工作表，因此放置以下代码：
ThisWorkbook.Sheets("SheetYouWantToProtect").Protect _
Password:="SheetPassword"
Of course, you can do this with more than one sheet if multiple sheets need to be unprotected. If each sheet has the same password, place a simple loop at the beginning of your Openevent:
当然，如果需要取消保护多张纸，您可以使用多张纸执行此操作。如果每个工作表都有相同的密码，请在Open活动开始处放置一个简单的循环：
Dim sht as Worksheet
For Each sht in Thisworkbook.Sheets
sht.Unprotect Password:="YourPassword"
Next sht
At the end of your code you can use the same loop to protect the sheets, just change .Unprotectto .Protect.
在代码末尾，您可以使用相同的循环来保护工作表，只需更改.Unprotect为.Protect.
Or, if there are many different passwords (aside from recommending you change them all to one password), you can place as many instances of the first line of code I mentioned as it takes to unprotect the necessary sheets.
或者，如果有许多不同的密码（除了建议您将它们全部更改为一个密码），您可以放置尽可能多的我提到的第一行代码的实例，因为它需要取消保护必要的工作表。
Note:There are manymore optional arguments to the .Protectmethod which I did not cover, but you can find an exhaustive list here.
注意：该方法还有更多可选参数.Protect，我没有介绍，但您可以在此处找到详尽的列表。
EDIT:Here'smore info on the Unprotectmethod.
回答by Mark Fitzgerald
Yes, it can be done
是的，可以做到
Private Sub Workbook_Open()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Sheets
WS.Unprotect Password:="YourPassword"
WS.Protect Password:="YourPassword", UserInterfaceOnly:=True
Next WS
End Sub
The UserInterfaceOnly:=Trueallows VBA to act on any part of your worksheets while the common user can only interact with "unlocked" cells.
这UserInterfaceOnly:=True允许 VBA 对工作表的任何部分进行操作，而普通用户只能与“解锁”单元格进行交互。
