0

I want to write a function that will unprotect a VBA project for a workbook that's open in the same instance as the workboook that has this function. I'm trying to adapt Siddharth Rout's code in this answer, but I can't figure out how to launch the specific VBA Properties window for the VBProject that I'm interested in unlocking.

As a test, I'm trying to iterate through the VBProjects in the instance to see which one corresponds to the desired workbook, and then open the VBA Properties window for that project:

Sub findVBProj()    

Dim prj As VBProject
Dim correctPrj As VBProject


For Each prj In Application.VBE.VBProjects
    If prj.filename = "C:\Users\xyz\Desktop\testUnlock.xlsm" Then
        Set correctPrj = prj
    End If
Next

correctPrj.VBE.CommandBars(1).FindControl(id:=2578, recursive:=True).Execute

End Sub

But this doesn't launch the VBA Properties window for correctPrj and therefore trigger the password prompt; instead, it opens the properties window for the project containing the module that contains findVBProj(), which makes sense because that module is selected when I run findVBProj(). How do I make the properties window for correctPrj open?

Community
  • 1
  • 1
sigil
  • 9,370
  • 40
  • 119
  • 199
  • As Siddarth Rout clearly states in his accepted answer (my emphasis): "however you have to ensure that workbook for which you want to un-protect the VBA **has to be opened in a separate Excel Instance**.". – Pieter Geerkens Jul 25 '14 at 03:24
  • If you're going to do it in the same instance you need code like `Set Application.VBE.ActiveVBProject = correctPrj`. You don't actually need separate instances. – Rory Jul 25 '14 at 10:16
  • @Rory, have you tested this? I was able to set the ActiveVBProject to `correctPrj`, but then the `.Execute` opened the Properties window for the main workbook instead. – sigil Jul 25 '14 at 15:57
  • Yes - it works fine for me in 2010. (and I have used similar code in 2003 in the past) – Rory Jul 26 '14 at 07:30

0 Answers0