Results 1 to 6 of 6

Thread: "VBA project password" window opens after closing the excel workbook

  1. #1

    "VBA project password" window opens after closing the excel workbook



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I have a workbook where one of the sheets is password protected. Apart from this, the code is also password protected.
    I use a userform to show the "Please wait" message to the user when the code is running.
    I use a ADODB connection to the same excel workbook to query the sheet.

    My problem is when i close the workbook, the "VBA project password" window is kept opened twice. I need to click "cancel" twice so get rid of this.

    I tried looking into many forums and most the answers were related to some Add-ins. I checked everything and there are no add-ins that are enabled.
    Apart from this, many forums has answers that "Google desktop" or Acrobat player may cause the problem. But in my case, i dont even have them.

    I also read a article that it may be due to memory leaks. If that is the case with my code, then please let me know how to fix this memory leak.

    Could someone please help me in fixing this?

    Thanks
    Sarathi

  2. #2
    I am having the exact same issue. I too was able to track the problem down to UserForms within protected code in a VBProject. If anywhere in your code you are changing ANY properties within the UserForm (e.g. .Top, .Caption) and then Exit or Close Excel then this problem occur - this has nothing to do with DAO or Google (not sure how that started). Strangely you have to choose Menu item File-Close-Exit or File-Exit to see this annoying VBProject Password Prompt - it is VERY repeatable. If you choose the Red [X] in the upper right-hand corner then this problem does NOT occur - this too is VERY repeatable. So sorry, I don't have a solution yet but I thought that I would post the exact circumstances to allow others to repeat this bug. I've tried multiple combinations of Userform commands and also changed the order with no success (UserForm: Load, Show, Hide, Unload, New) with no success.

    How to Reproduce the BUG (5mins):
    To reproduce this bug anyone can simple create a new MacroEnabled workbook, 1) Add a Userform with a close button, 2) Protect the VBProject code (this is critical), 3) Add code to Load or .Show the Userform in the WB Open or Activate procedure, 4) Add Userform property changing code (e.g. .Top=, .Left=, .Caption=) in the Userform Init procedure (this is critical), 5) Save & Close the workbook. 6) Relaunch the workbook ensuring that the Userform opens and then close it via the button you provided on the Userform. 7) Exit the WB via Menu option File-Exit (this is critical). Wait for it, wait for it (about 5 secs) :-) the VBProject Password Prompt to appear, repeatable.

    This should be easy to resolve as the problem is very repeatable - I've spent 2 days with no success. Not changing UserForm properties is not an option since I am centering the Userform on a dual screen PC - centering is not an issue on a single screen PC. So my next approach is to see if I can create NEW UserForms on the fly in the code and then destroy it when I am done. I'll post if this is successful.

    Thanks for posting and to anyone who has a solution to this repeatable problem.

    Randall

  3. #3

    Annoying VBProject Password Prompt

    Ok, I stand corrected on this issue. I searched the web and found statements that Adobe Acrobat Addin was the cause of this problem and I blatently ignored that advise because it sounded too far fetched. Well, troubleshooting this bug wore me down to the point that I decided to try anything and 1st on my list was going to the COM Addin in Excel and unchecking/removing the Adobe Acrobat AddIn. And sure enough the problem was solved. Adobe needs to fix their app.

    Adobe needs to look at Microsofts KB article# 280454.

    I hope this helps,

    Randall

  4. #4
    Not sure if others are still searching for a solution to this problem. Some of our users were also experiencing this issue and was found not to be caused by any Add-in issues.
    We tried the following workaround for the problem and it has resolved the issue for our users. Would be interested to see if it also solves the problems for others...

    The work around basically replaced the standard Excel close behaviour by a controlled close within the Workbook_BeforeClose event

    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)  
    Dim intResponse                     As Integer
    
    
        'If the workbook needs to be saved then ask the user if they want to save the workbook, if not then exit without saving
        'Need a global boolean to ensure the request to save the workbook is not shown twice
        If Not ThisWorkbook.Saved And Not blnStartedClose Then
            blnStartedClose = True
            intResponse = MsgBox("Do you want to Save the this Workbook" & vbNewLine & vbNewLine & _
                                "Select 'Yes' to save the workbook" & vbNewLine & _
                                "Select 'No' to close without saving", vbYesNo, "Confirm - Workbook Save?")
            If intResponse = vbYes Then ThisWorkbook.Save
        End If
        
        'If the user has clicked on 'No' to save the workbook then reset the "Saved" property to TRUE so that when we exit this routine no attempt to save the workbook is made
        ThisWorkbook.Saved = True
        
    End Sub
    Regards
    Scott
    Last edited by scott56hannah; 2016-02-10 at 10:19 PM.

  5. #5

    Solved - excel vba password prompt on exit

    Hi the above solution did not solve my issue.
    I have solution and I hope that it may help some one out there.
    To understand the issue please visit Microsoft article 80454

    The Com Addlins references causes this issue. Try the below sub in Thisworbook:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call ManageAddInsInstallation
    End Sub

    Private Sub ManageAddInsInstallation()
    Dim i As Integer
    Dim AddInsIns() As Variant
    ReDim AddInsIns(Application.AddIns.Count) As Variant
    For i = 1 To Application.AddIns.Count
    AddInsIns(i) = "No"
    If Application.AddIns(i).Installed Then
    AddInsIns(i) = "Yes"
    Application.AddIns(i).Installed = False
    End If
    Next i
    For i = 1 To Application.AddIns.Count
    If AddInsIns(i) = "Yes" Then
    Application.AddIns(i).Installed = True
    End If
    Next i
    End Sub

  6. #6
    Quote Originally Posted by scott56hannah View Post
    Not sure if others are still searching for a solution to this problem. Some of our users were also experiencing this issue and was found not to be caused by any Add-in issues.
    We tried the following workaround for the problem and it has resolved the issue for our users. Would be interested to see if it also solves the problems for others...

    The work around basically replaced the standard Excel close behaviour by a controlled close within the Workbook_BeforeClose event

    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)  
    Dim intResponse                     As Integer
    
    
        'If the workbook needs to be saved then ask the user if they want to save the workbook, if not then exit without saving
        'Need a global boolean to ensure the request to save the workbook is not shown twice
        If Not ThisWorkbook.Saved And Not blnStartedClose Then
            blnStartedClose = True
            intResponse = MsgBox("Do you want to Save the this Workbook" & vbNewLine & vbNewLine & _
                                "Select 'Yes' to save the workbook" & vbNewLine & _
                                "Select 'No' to close without saving", vbYesNo, "Confirm - Workbook Save?")
            If intResponse = vbYes Then ThisWorkbook.Save
        End If
        
        'If the user has clicked on 'No' to save the workbook then reset the "Saved" property to TRUE so that when we exit this routine no attempt to save the workbook is made
        ThisWorkbook.Saved = True
        
    End Sub
    Regards
    Scott
    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, inspired by Scott's, but much smaller. 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:


    Code:
    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.
    Jon Peltier
    Peltier Technical Services, Inc.
    http://peltiertech.com

Tags for this Thread

Posting Permissions

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