I have found that it doesn't seem to be easy to set a VBA password
easily from within code. I figure that I probably have to go down the
send keys route and so have set up the following sub

Sub SetVBAPassword()
Dim strPassword As String
strPassword = "monkey"

Excel.SendKeys "%TE^{TAB}VP", False
Excel.SendKeys strPassword & "{TAB}" & strPassword, False
Excel.SendKeys "{Enter}", False
End Sub

My problem is that this code will only work if it is in the same
project as the one you want to set the password for. I want to be able
to set the password from another workbook but don't know how to do it.
Can someone please help me.

code previously posted by Bill Manville:

Sub TestProtect()
Workbooks.Add.SaveAs "C:\Temp\Book1.xls"
ProtectVBProject Workbooks("Book1.xls"), "Jack"
Workbooks("Book1.xls").Close True
End Sub

Sub TestUnprotect()
Workbooks.Open "C:\Temp\Book1.xls"
UnprotectVBProject Workbooks("Book1.xls"), "Jack"
End Sub

Sub UnprotectVBProject(WB As Workbook, ByVal Password As String)
Dim vbProj As Object

Set vbProj = WB.VBProject

'can't do it if already unlocked!
If vbProj.Protection < 1 Then Exit Sub

Set Application.VBE.ActiveVBProject = vbProj

' now use lovely SendKeys to quote the project password
SendKeys Password & "~~"
Application.VBE.CommandBars(1).FindControl(Id:=257 8,
recursive:=True).Execute
End Sub

Sub ProtectVBProject(WB As Workbook, ByVal Password As String)
Dim vbProj As Object

Set vbProj = WB.VBProject

'can't do it if already locked!
If vbProj.Protection = 1 Then Exit Sub

Set Application.VBE.ActiveVBProject = vbProj

' now use lovely SendKeys to set the project password
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & _
Password & "~"

Application.VBE.CommandBars(1).FindControl(Id:=257 8, _
recursive:=True).Execute

WB.Save
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

--
Regards,
Tom Ogilvyl

wrote in message
om...
I have found that it doesn't seem to be easy to set a VBA password
easily from within code. I figure that I probably have to go down the
send keys route and so have set up the following sub

Sub SetVBAPassword()
Dim strPassword As String
strPassword = "monkey"

Excel.SendKeys "%TE^{TAB}VP", False
Excel.SendKeys strPassword & "{TAB}" & strPassword, False
Excel.SendKeys "{Enter}", False
End Sub

My problem is that this code will only work if it is in the same
project as the one you want to set the password for. I want to be able
to set the password from another workbook but don't know how to do it.
Can someone please help me.