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!

Isn't the clue in the comments, that, if it is locked, this would unlock it? — Andy G, Nov 15 '18 at 16:21
I would recommend not using `SendKeys` or `FindControl`/`Execute`. There are methods that you can invoke directly. For example, [Worksheet.Protect](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.protect) — this, Nov 15 '18 at 16:29
@AndyG yes it does unlock also but this is notthe problem. The problem is that when I am generating a new file that has not been lock ever before, sometimes fails. — user123, Nov 15 '18 at 16:50
@this I am not willing to protect the sheet I am willing to protect the code of the excel file — user123, Nov 15 '18 at 16:51
@Meyerhofer Maybe because the current VB project is not active, see the edit to my answer. — Andy G, Nov 15 '18 at 16:52
@AndyG yup, now does work. It was the the vb project that was not active! ty! — user123, Nov 15 '18 at 17:02
FWIW [password-protecting a VBA project is useless](https://stackoverflow.com/questions/1026483/is-there-a-way-to-crack-the-password-on-an-excel-vba-project). Moreover, plain-text, hard-coded password is *even more* useless. This locking is security theater, anyone that wants to get to the code, will get to the code. Use [Unviewable+](https://www.thespreadsheetguru.com/unviewable-best-vba-project-password-protection/) if you want actual protection (I have no affiliation with that product). — Mathieu Guindon, Nov 15 '18 at 17:06
@MathieuGuindon Thanks Mathieu I am already aware of this but is not for an IT environment is more like just to avoid the user to mess everything more than to avoid the code being ripped off but thanks I will take a look at the link you gave me — user123, Nov 20 '18 at 13:29

Andy G · Accepted Answer · 2018-11-15T16:49:13.903

3

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.

edited Nov 15 '18 at 16:49

answered Nov 15 '18 at 16:32

Andy G

19,232
5
47
69

Ok yes, you're right about this check but is not solving the issue. The problem I'm having is that somehow the macro is like changes context and maybe does the CTRL+TAB in another place rather than in the VBA Project properties tab so it messes the whole workflow. Can I somehow force VBA to be atomic in the process or something? Also thanks for the alternative but we are looking for the created file to have some macros on it and so. – user123 Nov 15 '18 at 16:39
I still have questions about the premise though, "they want to lock reports we generate with macros". If you are creating reports with macros why do these reports, i.e. workbooks, have code in them? – Andy G Nov 15 '18 at 16:50
I have an excel sheet that has many tabs on it (let's call it A). This A file has a button that generates a new excel file (B) with some tabs from A. What we want is to conserve the macros that tabs from A have so that we can have a file B with the same macros than in A but without other tabs that are in A that are not relevant to our clients. – user123 Nov 15 '18 at 16:54
2

Mmm I would consider creating, and locking, a single macro enabled template (.xltm) then creating your reports based on this template. You can control the template fully, then, when you create new reports based on it, the project files will already be locked. – Andy G Nov 15 '18 at 17:05

Locking a Project programmatically in VBA

1 Answers1

Linked