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/showthread.php?t=30687&highlight=open+excel+vba+project+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

[Gil]

Hello GTO
Yes, definitely, it all works well for me and many thanks for your help. I have added an example for anyone seeking a similar answer.
Also I would like to thank mdmackillop & xld for their input to the completion of this part of my project.

[GTO]

You are most welcome and I'm glad it's running smooth:beerchug:

[Bob Phillips]

Well done Mark, a nice piece of work :)

[GTO]

Thank you; unfortunately :o: No credit here, just remembered there was something about closing windows and looked til I found Ivan F Moala's note in #2 at http://www.mrexcel.com/forum/showthread.php?p=101971

I don't think I had seen this before; Set Application.VBE.ActiveVBProject = vbProj

What exactly is it doing?

I have only seen examples of sendkeys, but I happened upon this:

http://www.ozgrid.com/forum/showthread.php?t=13006&page=2 (#20)

Since I do not know French (somedays I barely know English!), not sure if I'll figure it out...

Have a great weekend :hi:

Mark

[GTO]

Not sure if bumping another fella's thread is okay, but reference:


...I don't think I had seen this before; Set Application.VBE.ActiveVBProject = vbProj

What exactly is it doing?...

I'm blaming sleep depravation; I think I have it. Am I correct in that the above get the keys sent to the correct project's properties dialog?

Thank you,

Mark

[Bob Phillips]

Yes, it settin g the activeproject.

[GTO]

Yes, it settin g the activeproject.

Thanks Bob; I figured my head was screwed on a bit better tonight - just wanted confirmation. VBIDE code seems a bit trickier to me with not being able to step through and see stuff happen (though trying to was a bit comical)

Me: "Close the window"
Excel: "You 'tard, I'm opening the window back up; you are stepping thru code..."
Me: "...uhhhhh...."