在VBA中以编程方式锁定项目 [英] Locking a Project programmatically in VBA

问题描述

在工作中，我们使用VBA，目前他们想锁定使用宏生成的报告.

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

我一直在尝试自动锁定项目(给定密码和工作簿名称)，并且部分成功地获得了以下代码块(我在那里找到的混合代码以及在SO中的某些问题) .可以通过某种方式手动进行操作(先访问vbaprojects属性，然后锁定).

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?

谢谢！

推荐答案

包括检查项目是否已受保护/锁定的检查，如果已被锁定，则不要尝试将其锁定

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.

这篇关于在VBA中以编程方式锁定项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！