ThisWorkbook.ChangeFileAccess xlReadWrite creates several VBAP objects for a book in a VBA window

Question

My book has the following open event:

Private Sub Workbook_Open() ThisWorkbook.ChangeFileAccess xlReadOnly End Sub 

And then this button:

 Sub UnlockDeveloper() Dim pwd As String pwd = InputBox("Enter developer password:", "Password") If pwd = "password" Then If ThisWorkbook.ReadOnly = True Then ThisWorkbook.ChangeFileAccess xlReadWrite End If Else MsgBox ("Incorrect password.") End If End Sub 

This all works fine, usually, but sometimes starting the UnlockDeveloper subsystem causes VBAProject to appear twice in the VBA window, and I have no way of knowing what the real file is. If I make changes to the wrong one, the changes will be lost as soon as I close Excel.

Anyone have any ideas on how to prevent this?

Answer 1

VBE will sometimes store "ghost projects" in VBE, even if the main document is closed. In this case, the ChangeFileAccess method closes the book (and leaves the Ghost project for it), and then opens a new copy of the book with the real project, but, as you can see, it is difficult to distinguish between Ghost and the real project.

So the main problem is saving the Ghost projects.

Phantom designs are usually triggered by an add supporting the link to the project. The host application (Excel) closes the node document and asks VBE to delete the project, but VBE sees that something still has a link to the project and therefore does not unload the project.

In my experience, this is usually a COM add that incorrectly contains a reference to the project (s). You can identify the culprit by disabling the COM add-ins one by one until the problem is more reproducible. Then re-enable add-ons that will not cause a problem. You may need to check out add-ins for Excel and VBE.

On my PC, the culprit was always the Power Query add-in, and disabling the add-in (and restarting Excel) always fixed the problem, but YMMV.