Lock Project via Macro?

[BAlGaInTl]

Hello all...

I have a workbook that I've written with quite a few security features. Thanks again to all those that have helped out here.

My question is this...

Technically, although I have written the VBA code, I'm not allowed to know the protecting password. That is for our QAD department only. I have set it up so that the powers that be can simply use a form to change the protect unprotect password. It is stored on an xlVeryHidden sheet. Now, I want QAD to be able to lock the project without having to give them complicated instructions.

Is there a macro available that can Lock/Unlock a project for viewing? Or will I just have to write up instructions for them to follow?

Thanks.

 

[BAlGaInTl]

Anyone?

 

[Ivan F Moala]

Here is an example of one way to do it.........

do a search for this on this site...there are many examples

<pre><FONT COLOR="#00007F">Option</FONT> <FONT COLOR="#00007F">Explicit</FONT>

<FONT COLOR="#007F00"><FONT COLOR="#007F00"><FONT COLOR="#007F00"><FONT COLOR="#007F00">'//////////////////////////////////</FONT></FONT></FONT>/</FONT>
<FONT COLOR="#007F00">'// Update Protected VBA project //</FONT>
<FONT COLOR="#007F00">'// By Ivan F Moala //</FONT>
<FONT COLOR="#007F00">'// 12th May 2002 //</FONT>
<FONT COLOR="#007F00"><FONT COLOR="#007F00"><FONT COLOR="#007F00">'//////////////////////////////////</FONT></FONT></FONT>
<FONT COLOR="#00007F">Dim</FONT> strPassWordVBA <FONT COLOR="#00007F">As</FONT> <FONT COLOR="#00007F">String</FONT>


<FONT COLOR="#00007F">Sub</FONT> TestUnprotect()
<FONT COLOR="#00007F">Dim</FONT> oWb <FONT COLOR="#00007F">As</FONT> Workbook

<FONT COLOR="#007F00">'// Lets get the password 1st!</FONT>
strPassWordVBA = GetPassWordString

<FONT COLOR="#007F00">'// Change Nmaes as Required</FONT>
<FONT COLOR="#00007F">Set</FONT> oWb = Workbooks.Open(Filename:="C:\ExcelFiles\Useful\VBE_ProtectedTest.xls")

<FONT COLOR="#00007F">If</FONT> UnProtectVBProject(oWb, strPassWordVBA) <FONT COLOR="#00007F">Then</FONT>
MsgBox "Project in workbook has been now unprotected"
<FONT COLOR="#00007F">Else</FONT>
MsgBox "Project in workbook has NOT been unprotected!"
<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">If</FONT>

ActiveWorkbook.ActiveSheet.Activate

<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">Sub</FONT>

<FONT COLOR="#00007F">Function</FONT> UnProtectVBProject(WB <FONT COLOR="#00007F">As</FONT> Workbook, strPassWord <FONT COLOR="#00007F">As</FONT> <FONT COLOR="#00007F">String</FONT>) <FONT COLOR="#00007F">As</FONT> <FONT COLOR="#00007F">Boolean</FONT>
<FONT COLOR="#00007F">Dim</FONT> vbProj <FONT COLOR="#00007F">As</FONT> <FONT COLOR="#00007F">Object</FONT>
<FONT COLOR="#007F00">'// Note lateBinding so NO reference to Extensibilty Lib</FONT>
<FONT COLOR="#00007F">Set</FONT> vbProj = WB.VBProject
<FONT COLOR="#007F00">'// Can't do it if already unlocked!</FONT>
<FONT COLOR="#00007F">If</FONT> vbProj.Protection <> 1 <FONT COLOR="#00007F">Then</FONT> <FONT COLOR="#00007F">GoTo</FONT> Done <FONT COLOR="#007F00">'Exit <FONT COLOR="#00007F">Function</FONT></FONT>
<FONT COLOR="#00007F">Set</FONT> Application.VBE.ActiveVBProject = vbProj
<FONT COLOR="#007F00">'// Now use SendKeys to send the project password</FONT>
SendKeys strPassWord & "~~"
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute

Done:
UnProtectVBProject = <FONT COLOR="#00007F">True</FONT>
<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">Function</FONT>

<FONT COLOR="#00007F">Sub</FONT> ProtectVBProject(WB <FONT COLOR="#00007F">As</FONT> Workbook, <FONT COLOR="#00007F">ByVal</FONT> strPassWord <FONT COLOR="#00007F">As</FONT> <FONT COLOR="#00007F">String</FONT>)
<FONT COLOR="#00007F">Dim</FONT> vbProj <FONT COLOR="#00007F">As</FONT> <FONT COLOR="#00007F">Object</FONT>

<FONT COLOR="#00007F">Set</FONT> vbProj = WB.VBProject
<FONT COLOR="#007F00">'// <FONT COLOR="#00007F">Is</FONT> it already locked!</FONT>
<FONT COLOR="#00007F">If</FONT> vbProj.Protection = 1 <FONT COLOR="#00007F">Then</FONT> <FONT COLOR="#00007F">Exit</FONT> <FONT COLOR="#00007F">Sub</FONT>
<FONT COLOR="#00007F">Set</FONT> Application.VBE.ActiveVBProject = vbProj
<FONT COLOR="#007F00">'// SendKeys to set the project password</FONT>
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & strPassWord & "{TAB}" & strPassWord & "~"
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
<FONT COLOR="#007F00">'// Close and Save</FONT>
WB.Close <FONT COLOR="#00007F">True</FONT>

<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">Sub</FONT>

<FONT COLOR="#00007F">Function</FONT> GetPassWordString() <FONT COLOR="#00007F">As</FONT> <FONT COLOR="#00007F">String</FONT>

GetPassWordString = InputBox("Please enter VBA Password", "VBA Password setting")
<FONT COLOR="#00007F">If</FONT> Len(GetPassWordString) = 0 <FONT COLOR="#00007F">Then</FONT> <FONT COLOR="#00007F">End</FONT>

<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">Function</FONT>
</pre>

 

[Ivan F Moala]

...........sorry......amend as required OR post back for further help.......