如何使用 VBA 保护 Excel 工作簿？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3690793/
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 to protect Excel workbook using VBA?
提问by Ravi
With a trigger like a check box I want to protect my work book. I tried Excel 2003:
使用复选框之类的触发器，我想保护我的工作簿。我试过 Excel 2003：
thisworkbook.protect("password",true,true)
thisworkbook.unprotect("password")
It's not working. Any suggestions?
它不起作用。有什么建议？
回答by Edward Leno
I agree with @Richard Morgan ... what you are doing should be working, so more information may be needed.
我同意@Richard Morgan ......你正在做的事情应该有效，所以可能需要更多信息。
Microsoft has some suggestions on options to protect your Excel 2003 worksheets.
Microsoft对保护 Excel 2003 工作表的选项提出了一些建议。
Here is a little more info ...
这里有更多信息......
From help files (Protect Method):
从帮助文件（保护方法）：
expression.Protect(Password, Structure, Windows)
expression Required.An expression that returns a Workbook object.
表达式 必需。返回 Workbook 对象的表达式。
Password Optional Variant.A string that specifies a case-sensitive password for the worksheet or workbook. If this argument is omitted, you can unprotect the worksheet or workbook without using a password. Otherwise, you must specify the password to unprotect the worksheet or workbook. If you forget the password, you cannot unprotect the worksheet or workbook. It's a good idea to keep a list of your passwords and their corresponding document names in a safe place.
密码可选变体。为工作表或工作簿指定区分大小写的密码的字符串。如果省略此参数，您可以在不使用密码的情况下取消保护工作表或工作簿。否则，您必须指定密码以取消保护工作表或工作簿。如果忘记密码，则无法取消对工作表或工作簿的保护。最好将您的密码及其相应文档名称的列表保存在安全的地方。
Structure Optional Variant.True to protect the structure of the workbook (the relative position of the sheets). The default value is False.
结构可选变体。True 保护工作簿的结构（工作表的相对位置）。默认值为 False。
Windows Optional Variant.True to protect the workbook windows. If this argument is omitted, the windows aren't protected.
Windows 可选变体。True 以保护工作簿窗口。如果省略此参数，则窗口不受保护。
ActiveWorkbook.Protect Password:="password", Structure:=True, Windows:=True
If you want to work at the worksheet level, I used something similar years ago when I needed to protect/unprotect:
如果你想在工作表级别工作，我在几年前需要保护/取消保护时使用了类似的东西：
Sub ProtectSheet()
ActiveSheet.Protect "password", True, True
End Sub
Sub UnProtectSheet()
ActiveSheet.Unprotect "password"
End Sub
Sub protectAll()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(1).Select
For i = 1 To myCount
ActiveSheet.Protect "password", true, true
If i = myCount Then
End
End If
ActiveSheet.Next.Select
Next i
End Sub
回答by MikeD
in your sample code you must remove the brackets, because it's not a functional assignment; also for documentary reasons I would suggest you use the
:=notation (see code sample below)Application.Thisworkbookrefers to the book containing the VBA code, not necessarily the book containing the data, so be cautious.
在您的示例代码中，您必须删除括号，因为它不是功能分配；同样出于文献原因，我建议您使用该
:=符号（请参阅下面的代码示例）Application.Thisworkbook指的是包含VBA代码的书，不一定是包含数据的书，所以要小心。
Express the sheet you're working on as a sheet object and pass it, together with a logical variable to the following sub:
将您正在处理的工作表表示为工作表对象并将其与逻辑变量一起传递给以下子项：
Sub SetProtectionMode(MySheet As Worksheet, ProtectionMode As Boolean)
If ProtectionMode Then
MySheet.Protect DrawingObjects:=True, Contents:=True, _
AllowSorting:=True, AllowFiltering:=True
Else
MySheet.Unprotect
End If
End Sub
Within the .Protectmethod you can define what you want to allow/disallow. This code block will switch protection on/off - without password in this example, you can add it as a parameter or hardcoded within the Sub. Anyway somewhere the PW will be hardcoded. If you don't want this, just call the Protection Dialog window and let the user decide what to do:
在该 .Protect方法中，您可以定义要允许/禁止的内容。此代码块将打开/关闭保护 - 在此示例中没有密码，您可以将其添加为参数或在 Sub 中进行硬编码。无论如何，PW 将被硬编码。如果您不想这样做，只需调用保护对话框窗口并让用户决定要执行的操作：
Application.Dialogs(xlDialogProtectDocument).Show
Hope that helps
希望有帮助
Good luck - MikeD
祝你好运 - MikeD
回答by KumaraPush
To lock whole workbook from opening, Thisworkbook.passwordoption can be used in VBA.
要从打开中锁定整个工作簿，Thisworkbook.password可以在 VBA 中使用选项。
If you want to Protect Worksheets, then you have to first Lock the cells with option Thisworkbook.sheets.cells.locked = Trueand then use the option Thisworkbook.sheets.protect password:="pwd".
如果要保护工作表，则必须先使用 option 锁定单元格，Thisworkbook.sheets.cells.locked = True然后使用 option Thisworkbook.sheets.protect password:="pwd"。
Primarily search for these keywords: Thisworkbook.passwordor Thisworkbook.Sheets.Cells.Locked
主要搜索这些关键字：Thisworkbook.password或Thisworkbook.Sheets.Cells.Locked
