6

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.

screenshot

Anyone got any ideas how to prevent this?

voromax
  • 3,369
  • 2
  • 30
  • 53
Stef Joynson
  • 222
  • 3
  • 10
  • 1
    Very interesting question, I recalled having seen something similar on Dicks Blog some years back, found [this](http://dailydoseofexcel.com/archives/2004/12/11/google-desktop/) which makes it clear there a variety of causes for the problem, and workarounds that have worked in some cases. – brettdj Dec 23 '14 at 11:53
  • 2
    As for why this happens, there is an [explanation on the msdn website](http://msdn.microsoft.com/en-us/library/office/ff193344%28v=office.15%29.aspx). I could not find a direct solution but I think the [build-in functionality of read-only mode](http://www.excel-easy.com/examples/read-only-workbook.html) can be useful? – Ioannis Dec 23 '14 at 14:29
  • Does [this](http://www.k2e.com/tech-update/tips/728-tips-adding-a-password-to-make-an-excel-workbook-read-only) make your coding obsolete? – OldUgly Apr 07 '16 at 19:17
  • I have tried under Excel 2016 for Windows and Excel 2011 for mac. I am unable to re-produce this issue. It appears to work as intended: opens as read only when first loaded, and once correct password is typed on a button, it makes it read and write enabled. When I go into VBA Developer, there is only one project there. I noticed that Excel closes the existing one, and opens it again when correct password is entered in read write mode. – waqasahmed May 08 '16 at 23:21

1 Answers1

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.

ThunderFrame
  • 9,352
  • 2
  • 29
  • 60