Hi Ajit

Only with Sendkeys it is possible

Read this old post from Bill Manville

------------------------
You will need to use SendKeys to unprotect the project(s) and reprotect
after replacing the modules.

Here's something to get you started:

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




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ajit" wrote in message ...
I am working on a situation where there are couple of hundred excel templates
with forms , modules etc. And i need to make a global change in the code to
one of the procedure. I found some valuable information at www.cpearson.com
which helped me pragramme to the Visual basic editor.

What i am doing is : I am opening the required template, finding the
procedure which needs to be replaced , deleting that procedure and adding a
new procedure with same name but different code.

All works well....except when VBE project is protected.

Could someone suggest a wayout to unprotect the project at runtime ...make
the required changes and then again protect the project before saving it.

Suggestions will be highly appreciated.

Thanks
--
Ajit

Hi Ajit,

Dunno an iota about send keys etc. But..
Is the password for all the files in your VB project ABC?

Regards,
Hari
India

"Ajit" wrote in message
...
Ron,
I had been working on unprotecting the project through sendkeys...it
works
fine if i do it on a single template...but when i try to use the same code
for more than one template in a folder it fails...

What i m doing is : through a loop ..opening each template in a folder and
passing the name of the template to the unprotect sub. For some reason
only
the last template in the folder is unprotected and rest are as is.....

Below is the code i m using

Set fs = Application.FileSearch
With fs
.LookIn = "C:\Documents and Settings\........location of the folder
.Filename = "*.xlt"
If .Execute 0 Then
filecount = .FoundFiles.Count
End If

For i = 1 To filecount
On Error Resume Next
Application.EnableEvents = False (I do this as there is a
form show on workbook open)
Workbooks.Open Filename:= _
.FoundFiles(i), Editable:=True

Application.EnableEvents = True
WB = Mid(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") + 1)

Call ProtectVBProject(Workbooks(WB), "ABC")


Next i

End With




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
End Sub




"Ron de Bruin" wrote:

Hi Ajit

Only with Sendkeys it is possible

Read this old post from Bill Manville

------------------------
You will need to use SendKeys to unprotect the project(s) and reprotect
after replacing the modules.

Here's something to get you started:

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




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ajit" wrote in message
...
I am working on a situation where there are couple of hundred excel
templates
with forms , modules etc. And i need to make a global change in the
code to
one of the procedure. I found some valuable information at
www.cpearson.com
which helped me pragramme to the Visual basic editor.

What i am doing is : I am opening the required template, finding the
procedure which needs to be replaced , deleting that procedure and
adding a
new procedure with same name but different code.

All works well....except when VBE project is protected.

Could someone suggest a wayout to unprotect the project at runtime
....make
the required changes and then again protect the project before saving
it.

Suggestions will be highly appreciated.

Thanks
--
Ajit