Locking a Project programmatically in VBA

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!

asked Nov 15 '18 at 16:16

Meyerhofer

4017

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

– 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.

– Meyerhofer
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

– Meyerhofer
Nov 15 '18 at 16:51

1

FWIW password-protecting a VBA project is useless. 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+ if you want actual protection (I have no affiliation with that product).

– Mathieu Guindon
Nov 15 '18 at 17:06

|
show 3 more comments

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

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!

asked Nov 15 '18 at 16:16

Meyerhofer

4017

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

– 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.

– Meyerhofer
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

– Meyerhofer
Nov 15 '18 at 16:51

1

FWIW password-protecting a VBA project is useless. 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+ if you want actual protection (I have no affiliation with that product).

– Mathieu Guindon
Nov 15 '18 at 17:06

|
show 3 more comments

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

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!

asked Nov 15 '18 at 16:16

Meyerhofer

4017

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

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!

excel vba excel-vba

asked Nov 15 '18 at 16:16

Meyerhofer

4017

asked Nov 15 '18 at 16:16

Meyerhofer

4017

asked Nov 15 '18 at 16:16

Meyerhofer

4017

asked Nov 15 '18 at 16:16

Meyerhofer

4017

asked Nov 15 '18 at 16:16

Meyerhofer

4017

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

– 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.

– Meyerhofer
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

– Meyerhofer
Nov 15 '18 at 16:51

1

FWIW password-protecting a VBA project is useless. 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+ if you want actual protection (I have no affiliation with that product).

– Mathieu Guindon
Nov 15 '18 at 17:06

|
show 3 more comments

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

– 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.

– Meyerhofer
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

– Meyerhofer
Nov 15 '18 at 16:51

1

FWIW password-protecting a VBA project is useless. 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+ if you want actual protection (I have no affiliation with that product).

– Mathieu Guindon
Nov 15 '18 at 17:06

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

– 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.

– Meyerhofer
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

– Meyerhofer
Nov 15 '18 at 16:51

FWIW password-protecting a VBA project is useless. 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+ if you want actual protection (I have no affiliation with that product).

– Mathieu Guindon
Nov 15 '18 at 17:06

|
show 3 more comments

1 Answer
1

active

oldest

votes

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

16.9k53757

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.

– Meyerhofer
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.

– Meyerhofer
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

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53323655%2flocking-a-project-programmatically-in-vba%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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

edited Nov 15 '18 at 16:49

answered Nov 15 '18 at 16:32

Andy G

16.9k53757

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.

– Meyerhofer
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.

– Meyerhofer
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

add a comment |

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

edited Nov 15 '18 at 16:49

answered Nov 15 '18 at 16:32

Andy G

16.9k53757

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.

– Meyerhofer
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.

– Meyerhofer
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

add a comment |

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

edited Nov 15 '18 at 16:49

answered Nov 15 '18 at 16:32

Andy G

16.9k53757

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

edited Nov 15 '18 at 16:49

answered Nov 15 '18 at 16:32

Andy G

16.9k53757

edited Nov 15 '18 at 16:49

answered Nov 15 '18 at 16:32

Andy G

16.9k53757

answered Nov 15 '18 at 16:32

Andy G

16.9k53757

answered Nov 15 '18 at 16:32

Andy G

16.9k53757

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.

– Meyerhofer
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.

– Meyerhofer
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

add a comment |

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.

– Meyerhofer
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.

– Meyerhofer
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

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.

– Meyerhofer
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.

– Meyerhofer
Nov 15 '18 at 16:54

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

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vfrdtyky