excel - VBA Project Password-Protect with SendKeys not Working Correctly

Question

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_Open event and a Workbook_BeforeClose event to the new workbook (to make certain sheets xlVeryHidden depending on level of access),
5. Runs a subprocedure from the newly imported module,
6. Saves and closes the workbook.

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 VBEditor and unhide the xlVeryHidden sheets in the workbook very easily.

So, my thought was to password protect the new workbook's VBAProject programmatically (see above: step number five). I found a few sources online of how to use SendKeys to achieve this goal (see below), but SendKeys is 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 VBAProject properties window(s) from the earlier code are all open and try to execute at the same time which crashes 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 post where this link to a non-SendKeys approach 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...

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

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

Answer 1

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.

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

This SO post
This blog

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