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

[Sarathi_57]

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

[RTRoy]

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

[RTRoy]

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

[scott56hannah]

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

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

[suchitra]

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

[Jon Peltier]

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

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:

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.

[pike]

Hi Gents,
Found that this worked for me. Maybe workbook specific!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  
 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

ThisWorkbook.Saved = True
 End Sub

[mikepfly2]

I'm having the same issue. Disabling the Bluebeam plugins and Adobe Acrobat plugins were the only way for me to resolve. I've contacted Bluebeam to see if they will let me help them write their add-in. They are not releasing the Excel objects properly.