Unlocking several VBAProjects

[Gil]

Hello
I have several VBAProjects that I need to unlock for editing, in bulk it will be timesaving.
I was looking at this code suggested by macropod for unlocking several VBAProjects on the following post
http://www.vbaexpress.com/forum/show...roject+in+bulk

Option Explicit

Sub UnprotectVBProj(ByVal Pwd As String)
    Dim vbProj As Object
    Set vbProj = ThisWorkbook.VBProject
    If vbProj.Protection <> 1 Then Exit Sub ' already unprotected
    Set Application.VBE.ActiveVBProject = vbProj
    SendKeys Pwd & "~~"
    Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
End Sub

When I apply it to a sample workbook and try to run it in VBA editor, it asks for a Macro name.
Forgive my ignorance but what are SendKeys
Do you substitute "~~" for "my pasword"
Also it has code VB,should it not be VBA

[Bob Phillips]

Worked fine for me.

VBA is VB, just within a host application.

[mdmackillop]

Where a routine contains a parameter, it needs to be called by another routine which supplies that parameter

 
Option Explicit

Sub Test()
    Call UnprotectVBProj("MyPassword")
End Sub
 
Sub UnprotectVBProj(ByVal Pwd As String)
    Dim vbProj As Object
    Set vbProj = ThisWorkbook.VBProject
    If vbProj.Protection <> 1 Then Exit Sub ' already unprotected
    Set Application.VBE.ActiveVBProject = vbProj
    SendKeys Pwd & "~~"
    Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
End Sub

[Gil]

Hello xld
Sorry but your reply does not help 'Worked fine for me'. I put my hands up and surrender as a novice. If you do do not like me I will leave the forum, reluctantly.
Gil

[Gil]

Hello mdmackillop
Thank you for your reply, I am trying to see where to fit your response in. If you could give me a prompt it will help me.
Gil

[GTO]

Hi Gil,

Show us the code that you are calling this procedure with.

Mark

[Bob Phillips]

Hello xld
Sorry but your reply does not help 'Worked fine for me'. I put my hands up and surrender as a novice. If you do do not like me I will leave the forum, reluctantly.
Gil

That's a tad paranoid response is it not. I read your post, tried it, had no problem, so I relayed this to you.

[Gil]

Hello xld
No paranoia. I thought my post had several questions that could be answered beginning with the first
'When I apply it to a sample workbook and try to run it in VBA editor, it asks for a Macro name'
I tried it in 2007 and that is the message it gave
Please give a step by step how you ran it.
Gil

[Gil]

Hello GTO
Perhaps this is my problem. I am not sure what what you mean when you ask
'Show us the code that you are calling this procedure with' I thought the code I had posted was sufficient for the task, I just didn't understand how to use it.
Gil

[Bob Phillips]

Hello xld
No paranoia. I thought my post had several questions that could be answered beginning with the first
'When I apply it to a sample workbook and try to run it in VBA editor, it asks for a Macro name'
I tried it in 2007 and that is the message it gave
Please give a step by step how you ran it.
Gil

Gil, to be honest, I think Malcolm (mdmackillop) hit the nail on the head.

It worked for me because I created a small procedure to call your procedure, passing it the password as a parameter. I did not try to run your procedure directly as I now suspect (and as MD assumed) you had done.

[Gil]

Thank you xld
Please do not misunderstand my intentions. I highly respect the support you & others offer in the forum. However just sometimes take a step back to when you first started on the vba experience. You quote 'as md assumed' and I admit I did not run the procedure but that is where my knowledge is lacking.All I am asking is for guidance on how to progress step by step.
Again many thanks for your patience.
Gil

[Bob Phillips]

Read MDs post again, I think (admittedly from my position of a lot more experience than you have) that he has explained it all. If you don't get it, respond with respect to what MD has said and we can see if we can take it from there.

[Gil]

Hello mdmackillop
Thank you for the addition to the code. It works fine. It only unlocks the active workbook so I have been looking on the web to see if I can work out how to open several projects. I haven't found anything hence this post.
Your continuing support and help is greatly appreciated.
Gil

[Gil]

Hello xld
Sorry. I have put all the toys back in my pram and look forward to your assistance in the future.
Gil

[Bob Phillips]

You need to go through all of the books in a loop, something like

Sub UnprotectThemAll()
    Dim wb As Workbook
    For Each wb In Application.Workbooks
        Call UnprotectVBProj(wb, "myPassword")
    Next wb
End Sub

 
Sub UnprotectVBProj(ByRef wb As Workbook, ByVal Pwd As String) 
    Dim vbProj As Object 
    Set vbProj = wb.VBProject 
    If vbProj.Protection <> 1 Then Exit Sub ' already unprotected
    Set Application.VBE.ActiveVBProject = vbProj 
    SendKeys Pwd & "~~" 
    Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute 
End Sub

I haven't tested this, it may need tweaking.

[Gil]

Hello xld
I have been experimenting with your code and it still only wants to open one VBA Project at a time. I have now attached a winzip which contains a sheet marked OPEN ME and some Example workbooks. The password is "mypassword"
If you can help once again I would appreciate it.
Gil

[Gil]

Hello
After several attempts to make any sense out of this I am still stuck. The example file in my last post runs and looks like it is opening the other VBA project files but only one opens. If you run it again it will open the next and so on.
Any help would be appreciated.
Gil

[GTO]

No promises, but this seems to work.

Sub UnprotectThemAll()
    Dim WB As Workbook
    For Each WB In Application.Workbooks
        If Not WB.Name = ThisWorkbook.Name Then Call UnprotectVBProj(WB, "mypassword")
    Next WB
End Sub
    
Sub UnprotectVBProj(ByRef WB As Workbook, ByVal Pwd As String)
    Dim vbProj As Object
    Dim OpenWindow As Object  ' As VBIDE.Window
    For Each OpenWindow In Application.VBE.VBProjects.VBE.Windows
        If InStr(1, OpenWindow.Caption, "(Code)") > 0 Then OpenWindow.Close
    Next
    DoEvents
    Set vbProj = WB.VBProject
    If vbProj.Protection <> 1 Then Exit Sub ' already unprotected
    Set Application.VBE.ActiveVBProject = vbProj
    DoEvents
    SendKeys Pwd & "~~"
    Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
End Sub

Hope that helps,

MArk

[Gil]

Hello GTO

Yes GTO it does look promising. I have applied it to my example file and all 5 vba open ok, perfect just what I wanted. One observation is if I set the password for OPEN ME and run the code it opens the 5 but not the OPEN ME. Any thoughts.
Later I will apply it to my project and see what the results are and update the thread.
Many thanks GTO for the help on this one.
Gil

[GTO]

...One observation is if I set the password for OPEN ME and run the code it opens the 5 but not the OPEN ME. Any thoughts....

Hi Gil,

Take out the IF test here:

    For Each WB In Application.Workbooks 
            If Not WB.Name = ThisWorkbook.Name Then Call UnprotectVBProj(WB, "mypassword") 
    Next WB

Mark