ThisWorkbook.ChangeFileAccess xlReadWrite Creates Multiple VBAProjects for workbook in VBA Window

Question

My workbook 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 running the UnlockDeveloper sub causes the VBAProject to appear twice in the VBA window, and I have no way of knowing which is the real file. If I make changes in the wrong one the changes are lost as soon as I close Excel.

Anyone got any ideas how to prevent this?

Answer 1

The VBE will sometimes keep "Ghost Projects" in the VBE, even if the host document has closed. In this instance, the ChangeFileAccess method is closing the workbook (and leaving a Ghost Project for it), and then opening a new instance of the workbook with a real Project, but as you observe, it's hard to differentiate between the Ghost and the real project.

So, the underlying problem is the persistence of Ghost projects.

Ghost projects are typically caused by an add-in maintaining a reference to a project. The host application (Excel) closes the host document, and asks the VBE to remove the project, but the VBE sees that something still has a reference to the project, and so does not unload the project.

In my experience, it is usually a COM addin that is incorrectly holding a reference to the project(s). You can identify the culprit by disabling COM add-ins one-by-one, until the problem is no longer reproducible. Then re-enable the add-ins that don't cause the problem. You may need to check the add-ins for Excel and for the VBE.

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