发送密钥无法正常工作的 VBA 项目密码保护

VBA Project Password-Protect with SendKeys not Working Correctly

提问by ARich

I've spent the last two days working on this problem. Most of the content I've found on this topic doesn't address the issue I'm having, so I'm hopeful that someone here can help me.

最近两天我一直在研究这个问题。我在这个主题上找到的大部分内容都没有解决我遇到的问题，所以我希望这里有人可以帮助我。

I've been working on some code that does the following from a "master scorecard" workbook:

我一直在编写一些代码，这些代码从“主记分卡”工作簿中执行以下操作：

1. Takes each "student" sheet in the workbook and copies the sheet into a new workbook,
2. Does a few minor manipulations of the new workbook,
3. Imports a module of code into the new workbook,
4. Adds a Workbook_Openevent and a Workbook_BeforeCloseevent to the new workbook (to make certain sheets xlVeryHiddendepending on level of access),
5. Runs a subprocedure from the newly imported module,
6. Saves and closes the workbook.

1. 获取工作簿中的每个“学生”工作表并将工作表复制到新工作簿中，
2. 对新工作簿做一些小的操作，
3. 将代码模块导入新工作簿，
4. 向新工作簿添加一个Workbook_Open事件和一个Workbook_BeforeClose事件（xlVeryHidden根据访问级别制作某些工作表），
5. 从新导入的模块运行子过程，
6. 保存并关闭工作簿。

Each scorecard uses code to ensure that only the person whose name is on the scorecard can access it. I've used Environ("username")in the workbook events to ensure security, but as you well know, if one and understands how to run macros, he/she could merely open the VBEditorand unhide the xlVeryHiddensheets in the workbook very easily.

每个记分卡都使用代码来确保只有记分卡上的名字的人才能访问它。我已经Environ("username")在工作簿事件中使用以确保安全性，但众所周知，如果一个人了解如何运行宏，他/她只需打开VBEditor和取消隐藏xlVeryHidden工作簿中的工作表即可。

So, my thought was to password protect the new workbook's VBAProjectprogrammatically (see above: step number five). I found a few sources online of how to use SendKeysto achieve this goal (see below), but SendKeysis unreliable (at best) and isn't cooperating with my code. The code works like a charm if I run it by itself, but if I call it from another project using Run Macro:="filename!macroname"it doesn't set the protection. After the code has run and all the workbooks have been created, the VBAProjectproperties window(s) from the earlier code are all open and try to execute at the same time which crashes Excel.

所以，我的想法是以VBAProject编程方式密码保护新工作簿（见上文：第五步）。我在网上找到了一些关于如何使用SendKeys来实现这个目标的资源（见下文），但SendKeys不可靠（充其量）并且不与我的代码合作。如果我自己运行它，该代码就像一个魅力，但如果我从另一个使用Run Macro:="filename!macroname"它的项目中调用它，则不会设置保护。在代码运行并创建所有工作簿VBAProject后，早期代码中的属性窗口都打开并尝试同时执行，这会导致 Excel 崩溃。

Sub LockVBAProject()

Const VBAProjectPassword As String = "123"
Dim VBP As VBProject, openWin As VBIDE.Window
Dim wbActive As Workbook
Dim i As Integer
    Set wbActive = ActiveWorkbook
    Set VBP = wbActive.VBProject
    Application.ScreenUpdating = False
        ' close any code windows to ensure we hit the right project
        For Each openWin In VBP.VBE.Windows
            If InStr(openWin.Caption, "(") > 0 Then openWin.Close
        Next openWin
        wbActive.Activate
With Application
    '//execute the controls to lock the project\
    .VBE.CommandBars("Menu Bar").Controls("Tools") _
        .Controls("VBAProject Properties...").Execute
    '//activate 'protection'\
    .SendKeys "^{TAB}"
    '//CAUTION: this either checks OR UNchecks the\
    '//"Lock Project for Viewing" checkbox, if it's already\
    '//been locked for viewing, then this will UNlock it\
    .SendKeys "{ }"
    '//enter password\
    .SendKeys "{TAB}" & VBAProjectPassword
    '//confirm password\
    .SendKeys "{TAB}" & VBAProjectPassword
    '//scroll down to OK key\
    .SendKeys "{TAB}"
    '//click OK key\
    .SendKeys "{ENTER}"
    'the project is now locked - this takes effect
    'the very next time the book's opened...
    End With


ThisWorkbook.SaveAs Filename:=Sheets(Sheets.Count).Name, FileFormat:=xlOpenXMLWorkbookMacroEnabled

Debug.Print "It Worked " & Now()

End Sub

I'm not sure why this is happening; like I said, the code works fine when run on its own. I found this postwhere this linkto a non-SendKeysapproach was outlined, but it was written several years ago and I'm not sure how I'd need to modify it for my purposes since I've never coded in VB6...

我不确定为什么会这样；就像我说的，代码在单独运行时工作正常。我找到了这篇文章，其中概述了指向非SendKeys方法的链接，但它是几年前写的，我不确定我需要如何修改它以达到我的目的，因为我从未在 VB6 中编码过......

Are there any thoughts as to why the SendKeysmethod is bunching up after the code has already run instead of executing when it's supposed to during the code? Should I abandon SendKeysin favor of this other method? I'm at a loss, so any help will be much appreciated!

有没有想过为什么该SendKeys方法在代码已经运行后堆积起来，而不是在代码期间应该执行的时候？我应该放弃SendKeys支持这种其他方法吗？我不知所措，所以任何帮助将不胜感激！

EDIT: I think the reason the code isn't working is because the correct project isn't activated at the time the SendKeyscode is executed. I had hoped that activating the proper workbook would solve the issue, but it doesn't appear to have helped.

编辑：我认为代码不起作用的原因是因为在SendKeys执行代码时没有激活正确的项目。我曾希望激活正确的工作簿可以解决问题，但似乎没有帮助。

采纳答案by ARich

Ok, so after another couple of hours of searching the web for alternative methods to achieve my goal, I stumbled across this post.

好的，所以在又在网上搜索了几个小时以寻找实现我的目标的替代方法之后，我偶然发现了这篇文章。

I created a template workbook (with the event code already in ThisWorkbook), password protected the project, and modified my code to use the template workbook for each new sheet. Now when the sheets are created, the project is already locked for viewing and requires a password. While I realize the security under this approach isn't very secure, it will help "keep honest people honest" as they say.

我创建了一个模板工作簿（事件代码已经在 中ThisWorkbook），对项目进行密码保护，并修改了我的代码以使用每个新工作表的模板工作簿。现在，当创建工作表时，项目已被锁定以供查看并需要密码。虽然我意识到这种方法下的安全性不是很安全，但正如他们所说的那样，它将有助于“让诚实的人保持诚实”。

For those who stumble across this post and still wish to programmatically lock/unlock their VBA Project, see these resources:

对于那些偶然发现这篇文章并仍然希望以编程方式锁定/解锁他们的 VBA 项目的人，请参阅以下资源：

This SO post
This blog

这个 SO 帖子
这个博客

Both are great resources that walk through a way to do it in VBA.

两者都是很好的资源，可以通过一种方法在 VBA 中做到这一点。

回答by MVW

To add a nuance to the otherwise fine piece of code originally posted here: If you change the Project Name for the workbook within the VBE, you'll need to change one line of code to:

要为最初发布在此处的其他精美代码添加细微差别：如果您在 VBE 中更改工作簿的项目名称，则需要将一行代码更改为：

   .VBE.CommandBars("Menu Bar").Controls("Tools") _
        .Controls(VBP.Name & " Properties...").Execute

(sigh)

（叹）