Prompt for VBA Project password after Excel closes with Dropbox Badge

[MontRK]

Many of my excel projects are stored in a Dropbox folder through the Dropbox desktop app on PC, windows 7 and 8.1, Excel 2013, 2016. Dropbox recently updated to include Dropbox Badge when excel files from a Dropbox folder are open in excel. When Dropbox Badge was implemented I started getting a prompt for a VBA Project password after excel closes in some of my excel projects. This occurs in both versions of windows and both versions of Excel when all of the following conditions are met:

1. The workbook has a password protected VBA project
2. A userform is displayed sometime during the file's use.
3. The user closes the file after a change has been made and chooses to save the changes (saving separately before closing causes no error).
4. The file is stored in a Dropbox folder and Dropbox Badge is enabled.
5. (In some cases, having a msgbox prompt in the Workbook_BeforeClose event seems to cement the problem. Otherwise, it occasionally doesn't occur when closing.)

Here is a link to a simple file that replicates the problem when it is stored in a Dropbox folder on PC and Badge is active: https://www.dropbox.com/s/36s8ct1n2i5r5yg/Simple Password Prompt Generator.xlsm?dl=0
(its VBA Project password is "a")

It opens a userform in the Workbook_Open event. The Userform changes a cell value. The user clicks the command button to close the form. The user clicks to close excel. The Workbook_BeforeClose event shows a msgbox (though this can generally be removed and still produce the error). The user clicks OK in the msgbox. The user chooses to save changes to the file. Excel closes, and then the VBAProject Password prompt appears. Switching the Dropbox Badge setting from Always Show to Never Show alleviates the problem.

This problem is similar to that described in the microsoft knowledge base (https://support.microsoft.com/en-us/kb/280454) but doesn't involve a COM add-in (removing COM add-ins or setting their connections to false has no effect).

It has exactly the same symptoms as the problem with the Acrobat PDFMaker COM add-in several years ago (https://forums.adobe.com/message/5916268).

Is anyone else having the same problem? Is there anything I can do besides hope Dropbox implements a fix?

Thanks!

 

[BlokeMan]

Cross-post;Prompt for VBA Project password after Excel closes with Dropbox Badge

 

[Jon Peltier]

I'm going to ignore the cross-posting, because who knows which of many similar threads will appear first in a web search. In fact, since this is a widespread problem, I'm cross-posting my potential solution, but only in existing threads, not in any new ones.

This is a problem that has intermittently plagued my own Excel VBA add-ins for a small number of customers. I've documented the problem in my online documentation: VB Password Dialog - Peltier Tech Charts for Excel - Documentation.

While working on a specific situation for a client, I came up with a solution. I don't know if it only works for his situation (on just my machine) or if it is more widely applicable.

Insert the line "ThisWorkbook.Saved = True" at the end of the Workbook_BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' blah blah before close code

    ThisWorkbook.Saved = True
End Sub

If anyone has a chance to try this, could you let me know if it helps for you and/or your clients.

 

[brianfosterblack]

I'm going to ignore the cross-posting, because who knows which of many similar threads will appear first in a web search. In fact, since this is a widespread problem, I'm cross-posting my potential solution, but only in existing threads, not in any new ones.

This is a problem that has intermittently plagued my own Excel VBA add-ins for a small number of customers. I've documented the problem in my online documentation: VB Password Dialog - Peltier Tech Charts for Excel - Documentation.

While working on a specific situation for a client, I came up with a solution. I don't know if it only works for his situation (on just my machine) or if it is more widely applicable.

Insert the line "ThisWorkbook.Saved = True" at the end of the Workbook_BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' blah blah before close code

    ThisWorkbook.Saved = True
End Sub

If anyone has a chance to try this, could you let me know if it helps for you and/or your clients.

Thanks this works for me.