There appears to be an obscure glitch in VBA that affects certain XLSM files. If certain trigger conditions are met on the affected file, the VBA Password Prompt pops up after closing the workbook.
I'm not the only one to have this problem as can be seen by a few google searches. (Typical example: http://www.mrexcel.com/forum/excel-q...form-used.html )
What makes it difficult to find the real solution is that many of the so-called solutions out there are not correct (I've painstakingly eliminated each one) - therefore the happily solved threads have identified the wrong 'fix'.
On a known bad workbook, I can't get the glitch to trigger on any of these conditions (so far):
- Any procedures run did not involve the use of a UserForm (Displaying a UserForm appears to be one of the trigger conditions)
- XLSM has all UserForms removed (See above comment)
- XLSM has VBA Project password protection removed (Logically the Password Prompt wouldnt appear if the project wasnt currently locked for viewing)
- VBA Project password was entered before closing the XLSM (See above comment)
- The XLSM is converted to a XLAM. Glitch doesn't trigger on XLAM?
I have proved that the following fixes do not work:
- Setting ADO objects to Clear before setting to Nothing (Microsoft suggested fix https://support.microsoft.com/en-us/kb/280454 ) (I removed the ADO reference & modules out of the workbook. Still happens)
- Setting EnableEvents to False at Workbook_BeforeClose
- Setting DisplayAlerts to False at Workbook_BeforeClose
- Remove viewing password, save & close, add new viewing password, save&close&open. Error still occurs
- Setting ThisWorkbook.Saved to True at Workbook_BeforeClose
- Setting the file attribute Read-Only to True
I have proved that the following are not responsible for causing the problem:
- GoogleDesktop installed (or updating it). ( http://www.dbforums.com/showthread.p...Password/page2 )
- ADO (I removed the ADO reference & all related code some time ago, I still get the popup)
- DAO ( http://www.vbaexpress.com/forum/show...rompt-on-Close )
- Adobe Acrobat COM AddIn ( http://www.excelguru.ca/forums/showt...excel-workbook )
- Adobe PDF Maker AddIn
- APIs
- Classes
- Collections
- Excel 2013? Excel 64 bit? (Not 100% certain that these have nothing to do with it. While it seems to be possible to create the issue in a new XLSM created in Excel 2010 32 bit, I find it suspicious that the only person affected prior to this recent happening was running Excel 2013. And the sudden recent "infection" only occurred after several XLSMs were edited in Excel 2013 64 bit. My theory is that somehow the corrupted XLSMs have "infected" the PC running Excel 2010 32 bit??)
Any one have any suggestions as to what is causing this?
Bookmarks