+ Reply to Thread
Results 1 to 21 of 21

Closing workbook causes VBA Project Password Prompt to appear

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Thumbs down Closing workbook causes VBA Project Password Prompt to appear

    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):
    1. Any procedures run did not involve the use of a UserForm (Displaying a UserForm appears to be one of the trigger conditions)
    2. XLSM has all UserForms removed (See above comment)
    3. XLSM has VBA Project password protection removed (Logically the Password Prompt wouldnt appear if the project wasnt currently locked for viewing)
    4. VBA Project password was entered before closing the XLSM (See above comment)
    5. The XLSM is converted to a XLAM. Glitch doesn't trigger on XLAM?


    I have proved that the following fixes do not work:
    1. 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)
    2. Setting EnableEvents to False at Workbook_BeforeClose
    3. Setting DisplayAlerts to False at Workbook_BeforeClose
    4. Remove viewing password, save & close, add new viewing password, save&close&open. Error still occurs
    5. Setting ThisWorkbook.Saved to True at Workbook_BeforeClose
    6. Setting the file attribute Read-Only to True

    I have proved that the following are not responsible for causing the problem:
    1. GoogleDesktop installed (or updating it). ( http://www.dbforums.com/showthread.p...Password/page2 )
    2. ADO (I removed the ADO reference & all related code some time ago, I still get the popup)
    3. DAO ( http://www.vbaexpress.com/forum/show...rompt-on-Close )
    4. Adobe Acrobat COM AddIn ( http://www.excelguru.ca/forums/showt...excel-workbook )
    5. Adobe PDF Maker AddIn
    6. APIs
    7. Classes
    8. Collections
    9. 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?
    Last edited by mc84excel; 04-24-2015 at 01:16 AM. Reason: Update to include possible workaround
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Closing workbook causes VBA Project Password Prompt to appear

    I always just thought it was the screen not clearing itself, what do they call it, tag-ons?

    I see a lot of that particular question not getting answered.

  3. #3
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Closing workbook causes VBA Project Password Prompt to appear

    Quote Originally Posted by davesexcel View Post
    I always just thought it was the screen not clearing itself, what do they call it, tag-ons?
    Pardon my ignorance. What are tag-ons?

  4. #4
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Closing workbook causes VBA Project Password Prompt to appear

    All affected XLSMs were created in Excel 2010 32b. And this error only started occurring on them after some work was done to them in Excel 2013 64b. Coincidence?

    Could 64 bit and/or Excel 2013 be somehow corrupting compilation of these VBA Projects?

    UPDATE: Ruled the above possibility out (I think - see OP for my crazy infection theory!)

    I created a fresh XLSM in Excel 2010 32 bit and copied the modules & forms across from affected XLSM. It happens on the new XLSM = Disproves theory of corruption/glitch at Project level of XLSM

    Maybe copying the modules across is not a good idea. So I copied all the "bad" code to text editor, closed the bad XLSM. Created a new XLSM, copied the code from the editor and manually created new form controls. It happens on the new XLSM = disproves theory that corruption is carrying across in form control.
    Last edited by mc84excel; 04-23-2015 at 11:03 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Closing workbook causes VBA Project Password Prompt to appear

    I had another theory. Maybe I wrote something bad in the recent code revisions?

    So I created a new XLSM in Excel 2010 32 bit, imported older versions of the template BASs (several months old - and used in working "good" versions of the affected XLSMs).

    It still happens on the new XLSM = which may disprove theory re code revision (I did copy across none-template code but there was nothing extraordinary in that module)


    Now I'm really puzzled as to what the cause could be.

    The only way I can explain this is that the so-called 'good' XLSM versions also have the problem but I am not aware of this yet because they happen to be lacking one (as yet unknown) condition required to trigger the issue??

  6. #6
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Closing workbook causes VBA Project Password Prompt to appear

    What makes this problem very hard to test is that the error doesn't trigger on every close. I can open a XLSM, run a macro (displaying a form) and close. And I may be able to do this 3-4 times in succession without a problem. Then I get the problem the next two times. Yet I did the same actions on the same workbook each time! There must be another factor at work here. But what is it?

  7. #7
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Lightbulb Re: Closing workbook causes VBA Project Password Prompt to appear

    Now this is interesting.

    After many hours of futile debugging on XLSMs, I decided to try converting one to a XLAM. I've run the XLAM macro & closed Excel over and over again and so far I cant trigger the glitch on this workbook anymore!

    Unless anyone can prove me wrong, I suggest that the glitch can not be triggered on a XLAM. This could be a potential work-around for many desperate programmers driven mad by this bug.
    Last edited by mc84excel; 04-24-2015 at 01:27 AM.

  8. #8
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Post Re: Closing workbook causes VBA Project Password Prompt to appear

    If anyone wants to help out finding the cause of this glitch, here's a simple test XLSM for you to create (under 5 minutes to make)
    1. Create a new XLSM
    2. In the workbooks VBA editor, add 1x module & 1x UserForm
    3. Add two command buttons to the UserForm
    4. Copy the code at bottom of this post into the UserForm module
    5. Copy the code at the bottom of this post into the standard Module
    6. Add a button to the Sheet & assign the only visible macro to it
    7. Open the VBA Project options, Lock For Viewing & add a password
    8. Save & Close the XLSM

    To test the workbook, open it. Run the macro. Close Excel. Repeat (Don't have other workbooks open - this could hide and/or delay the appearance of the prompt)

    NB that the glitch does not trigger every time. So you will need to open/run/close several times in a row to see if it happens or not. (Now you know why I added a button to call the code!)

    Here's the UserForm code
    Please Login or Register  to view this content.
    Here's the standard module code
    Please Login or Register  to view this content.

  9. #9
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Closing workbook causes VBA Project Password Prompt to appear

    Your entire line of reasoning is based on the premise there could only be one cause for this symptom. Since you have no evidence for that assumption, I don't see how you can suggest that the other solutions you found aren't actually solutions, given that they worked.

    FWIW, I got bored after 10 attempts with no glitch.
    Last edited by romperstomper; 04-24-2015 at 05:55 AM.
    Remember what the dormouse said
    Feed your head

  10. #10
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Closing workbook causes VBA Project Password Prompt to appear

    Quote Originally Posted by romperstomper View Post
    Your entire line of reasoning is based on the premise there could only be one cause for this symptom. Since you have no evidence for that assumption...
    Good to hear from you again Rory!

    Believe it or not, but the idea that there may be more than one factor contributing to the problem did (eventually) occur to me over the weekend

    Of course, if the theory of multiple causes is correct then it will be exponentially harder to solve the problem. In which case, we may as well try to eliminate all of the 'single cause' possibilities first, no?


    Quote Originally Posted by romperstomper View Post
    ...I don't see how you can suggest that the other solutions you found aren't actually solutions, given that they worked.
    What I suggested was that those solved threads may have made multiple changes - one of which worked - and the OP identified the incorrect change.

    Of course there is another possibility - if we allow that there may be one factor at work causing the problem, then we have no reason to assume that there is only one change that fixes the problem. What if a combination of two or more changes are needed to fix it?

    Quote Originally Posted by romperstomper View Post
    FWIW, I got bored after 10 attempts with no glitch.
    I find it curious that you did it 10 times without a glitch. I just tried it now and got it first go. Did you have any XLAMs installed when you tested it? Did you have any other instances of Excel open? Did you choose a long or short password on the VB Project?

    The other thing I'm varying is the time taken to close the WB after running. For instance, closing the workbook ASAP after getting the Test Over msg. Or deliberately waiting 5-6 seconds after the Test Over msg before closing. I seem to find that varying the time may have an affect on the chance of the glitch occurring (The theory I'm testing is that there's a sweet spot in the time window. Too fast or too slow increases the chance of triggering the glitch)

  11. #11
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Closing workbook causes VBA Project Password Prompt to appear

    I'm uploading a workbook for anyone to test. I had to password protect the VB Project so it can be tested properly. If anyone wants to verify this code is safe before running - the password is "abc"

    My current theory as to the cause is that the code that opens/closes the form needs improving. I say this because I've noticed that this glitch only appears on XLSMs that have the "fndtmDatePicker" sub. (It's not the date picker itself as when I remove the Calendar class, the problem still occurs - as can be seen from the attached test workbook)

  12. #12
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Closing workbook causes VBA Project Password Prompt to appear

    More to the point - did you have any COM add-ins installed? Do you still have the issue if you run Excel in Safe mode?

  13. #13
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Closing workbook causes VBA Project Password Prompt to appear

    Quote Originally Posted by romperstomper View Post
    More to the point - did you have any COM add-ins installed?
    Well I was testing XLAM Add-Ins, not COM add-ins when I asked. But sure.

    Yes, I have "VBA Code Cleaner 5.0" and "Send to Bluetooth". The latter isn't ticked however. I've been using Code Cleaner for a very long time (long before this problem started happening to me) so I doubt that is the cause.

    Quote Originally Posted by romperstomper View Post
    Do you still have the issue if you run Excel in Safe mode?
    I didn't think of testing that. Thanks Rory. Will do and let you know

    UPDATE: Just tested now. Started Excel in safe mode. Opened workbook. Confirmed that Safe Mode appeared at top of Excel. Ran button. Closed workbook. Password prompt popped up again.
    Last edited by mc84excel; 04-28-2015 at 08:04 PM.

  14. #14
    Registered User
    Join Date
    06-17-2009
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    84

    Re: Closing workbook causes VBA Project Password Prompt to appear

    I had this issue yesterday as well. Code has worked for months with no issues. No change or added software. Windows may have done an update.
    For me it seemed to be related to add ins.
    This site solved my problem. https://www.add-ins.com/password_problem.htm

    Edit: Windows did an update on my computer on July 15, 2015 so far mine is the only affected running the identical code and shared spreadsheets.
    Last edited by Epscan; 07-17-2015 at 11:36 AM.

  15. #15
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Closing workbook causes VBA Project Password Prompt to appear

    Thanks for the suggestion Epscan. I will check this out and let you know how it goes.

  16. #16
    Registered User
    Join Date
    06-17-2009
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    84

    Re: Closing workbook causes VBA Project Password Prompt to appear

    I have recently posted the same question again as my above solution did not continue to work.
    I got an answer from one user and he suggested that Dropbox may be an issue. In my case this definitely is part of the problem.
    However if in my code I save the Active workbook before closing the VBA code then the error does not show up even if Dropbox is active.

    http://www.excelforum.com/excel-prog...ml#post4174723

  17. #17
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Closing workbook causes VBA Project Password Prompt to appear

    Quote Originally Posted by Epscan View Post
    I got an answer from one user and he suggested that Dropbox may be an issue. In my case this definitely is part of the problem
    Hi Epscan. I had an end user encounter the password issue earlier today on a RDP session. I checked out his logon and Dropbox is not installed on the server. (He had Dropbox on his local machine but he was using Excel on the server)

  18. #18
    Registered User
    Join Date
    02-03-2014
    Location
    Pakistan
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Closing workbook causes VBA Project Password Prompt to appear

    I am not using dropbox or anything but still getting this annoying password prompt everytime i close the workbook. I have set all the objects to nothing, I am also saving the workbook automatically but nothing seems to work

  19. #19
    Registered User
    Join Date
    08-04-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2007, 2010, 2013
    Posts
    10
    Quote Originally Posted by syedabdullah View Post
    I am not using dropbox or anything but still getting this annoying password prompt everytime i close the workbook. I have set all the objects to nothing, I am also saving the workbook automatically but nothing seems to work
    What, if any, add-ins do you have installed? I disabled the Bluebeam pdf add-in and the pop up went away.

    What version of excel are you using? Do you have multiple version of excel on the same computer?
    If you only have one version did you upgrade it (say from excel 2010 to 2016)? Some of my users were able to get rid of the pop up by completely uninstalling all of office and doing a full, clean install.

  20. #20
    Registered User
    Join Date
    02-03-2014
    Location
    Pakistan
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Closing workbook causes VBA Project Password Prompt to appear

    No add-ins installed, COM issue also checked, OLE Automation also un-checked, all variables set to Nothing. I have done every possible thing one can find on the internet. THe problem is that my file is on multiple clients computers and all of them are getting this VBA promopt when they close the file. I have also tried savi

  21. #21
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Closing workbook causes VBA Project Password Prompt to appear

    You may have to supply a stripped down version of the workbook so others can see the problem.

    In the mean time would it be possible to saveas an .xlsx file? Would that remove all the modules, then bring all the codes back in.

    By your previous post it looks like you saw the following web page, just incase you did not.

    https://www.vbapasswordremover.org/b...e-closing.html

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. User Form causing Prompt for VBA Project Password when closing Excel
    By avr5iron in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-16-2016, 07:16 PM
  2. VBA Project password prompt
    By satyakantiwal in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2011, 05:52 PM
  3. [SOLVED] Password prompt appears when closing Excel
    By RJ Lohan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-22-2005, 10:06 PM
  4. [SOLVED] VBA Project Password prompt on Exit
    By Malcolm Makin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-22-2005, 06:06 PM
  5. Replies: 0
    Last Post: 03-25-2005, 11:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1