Change VBA Project Properties via VBA Code?

Posted by JAF on March 07, 2000 7:30 AM

Here at work, we have a "Departmental Password" which is known to all staff within our department, but not to anyone else.

Following the move of one member of staff to another area, I am in the process of changing the password on all affected files (and we're talking a LOT of files!).

I have a macro to change the worksheet and workbook protection password, but is it possible to change the VBA Project password using VBA as well? I've tried recording the steps, but nothing shows up in the recorded macro.

Any assistance will greatly help my sanity!

Cheers
JAF

Posted by Ivan Moala on March 08, 2000 12:06 AM

Jaf
Here is one way by Bill Manville
Sub SetVBProjectPassword(WB As Workbook, ByVal Password As String)
' used during making of new protected app, if user changes password
Dim VBP As VBProject, oWin As VBIDE.Window
Dim wbActive As Workbook
Dim i As Integer

Set VBP = WB.VBProject
Set wbActive = ActiveWorkbook

'can't do it if locked!
If VBP.Protection = vbext_pp_locked Then Exit Sub

Application.ScreenUpdating = False

' close any code windows to ensure we hit the right project
For Each oWin In VBP.VBE.Windows
If InStr(oWin.Caption, "(") > 0 Then oWin.Close
Next oWin

WB.Activate
' now use lovely SendKeys to set the project password
Application.OnKey "%{F11}"
SendKeys "%{F11}%TE+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & _
Password & "~%{F11}", True

' leave no evidence of the password
Password = ""
' go back to the previously active workbook
wbActive.Activate
End Sub

Ivan

Posted by Keith on March 08, 2000 2:35 PM

I tried your code, but the compiler fused about "VBProject".

What DLL/File do you need to reference to use this object?

Posted by Ivan Moala on March 08, 2000 10:13 PM

You need to reference Visual Basic for Applications Extensibility Library.
Vbeext1.olb

Ivan

Posted by Keith on March 09, 2000 5:48 AM

Thanks for the quick response.