Locking a Project programmatically in VBA

Question

At work we use VBA and currently they want to lock reports we generate with macros.

I've been trying to lock a project automatically (given a password and workbook name) and I have partially succeded with the following chunk of code (a mix of codes I've found arround there and in some questions in SO). It is somehow doing what one would do manually (going vbaprojects properties and then locking).

Sub LockVBAProject(nameWorkbookForMarket As String, pw As String)
   With Workbooks(nameWorkbookForMarket).Application
       '//execute the controls to lock the project\\
       .VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).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\\
       '//enter password (password is 123 in this example)\\
        .SendKeys "{ }"

       .SendKeys "{TAB}" & pw
       '//confirm password\\
       .SendKeys "{TAB}" & pw
       '//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
End Sub

The problem with this code, is that sometimes works well and sometimes does not. I am a bit confused of this "undeterministic" behaviour. Could someone put shed some light on this issue?

Thanks!

Answer 1

Include a check to see if the project is already protected/locked, and don't attempt to lock it if it is already locked

If Workbooks(nameWorkbookForMarket).VBProject.Protection = 1 Then Exit Sub

You also want to ensure the correct project is active, so:

Set vbProj = Workbooks(nameWorkbookForMarket).VBProject

Set Application.VBE.ActiveVBProject = vbProj

If vbProj.Protection = 1 Then Exit Sub
' send keys

---

An alternative approach is to split the reports' process into two parts, keeping code in a separate workbook. It is unusual, in my view, that new reports/workbooks would continue to be created that include code.