vba 使用密码取消保护工作表，而不在宏中暴露密码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11981410/
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
Unprotect sheet with password, without exposing password in the macro
提问by Lock
I have recently started writing some Macro's in Excel. I have a protected worksheet, and a few buttons that allow the user to add/remove columns/rows at a certain point in the spreadsheet.
我最近开始在 Excel 中编写一些宏。我有一个受保护的工作表和一些按钮，允许用户在电子表格中的某个点添加/删除列/行。
I currently unprotect the worksheet, perform the functions and then protect the worksheet.
我目前取消保护工作表，执行功能，然后保护工作表。
The problem is, is that the protected sheet password is in plain text in the macro for any curious eyes to see.
问题是，受保护的工作表密码是宏中的纯文本，任何好奇的眼睛都可以看到。
How can I have a password on the sheet to protect it, but at the same time, allow my Macro to unprotect the spreadsheet whilst it performs its functions, and then reprotect it, without typing the plaintext password in the macro?
如何在工作表上设置密码来保护它，但同时允许我的宏在电子表格执行其功能时取消保护它，然后重新保护它，而无需在宏中输入明文密码？
回答by chris neilsen
Two options:
两种选择：
- Password protect the VBA. (While VBA protection is far from secure against malicious intent, it is no worse than the security of sheet protection)
If you set the worksheet protection using VBA you can specify
UserInterfaceOnly:=True.sh.Protect Password:="Password", UserInterfaceOnly:=TrueOnce set in this way VBA code can modify the sheet without supplying a password. Since the password must be supplied once to apply protection in the first place, run this code from a seperate workbook or addin you keep to yourself.
- 密码保护 VBA。（虽然 VBA 保护远非针对恶意意图的安全，但它不比工作表保护的安全性差）
如果使用 VBA 设置工作表保护，则可以指定
UserInterfaceOnly:=True.sh.Protect Password:="Password", UserInterfaceOnly:=True一旦以这种方式设置，VBA 代码就可以在不提供密码的情况下修改工作表。由于首先必须提供一次密码才能应用保护，因此请从单独的工作簿或您自己保留的插件运行此代码。
回答by brettdj
You can call a UserFormto handle the password prompt, but mask the characters by using PasswordCharas a textbox property.
您可以调用 aUserForm来处理密码提示，但通过PasswordChar用作文本框属性来屏蔽字符。
回答by Oliver Lockett
I found some code a while back which may be of some help. This will unlock any password protected worksheet. It takes a little time depending on the length of the password, but essentially it just sledgehammers its way through, and unlocks the worksheet. May not be the most efficient answer to your question, but its a useful bit of code to have nonetheless.
不久前我发现了一些可能有帮助的代码。这将解锁任何受密码保护的工作表。根据密码的长度，它需要一点时间，但基本上它只是大锤通过，并解锁工作表。可能不是您问题的最有效答案，但它仍然是一些有用的代码。
Sub PasswordBreaker()
'Breaks worksheet password protection.
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub
