VBA Project Password Dialogue Box Shown after Closing Excel when Acrobat PDFMaker COM Addin is Enabled
I have developed many applications the last 20 years in different environments (ASP, SQL, VBA, VB, etc) . Recently I have had reports about a bug that I believe derives from a com add-in called “Acrobat PDFMaker Office COM Addin” (PDFMaker.OfficeAddin) developed by Adobe.
The application that generates this problem is built in Excel. It has a custom Userform with a listbox in it. The position of the listbox is different depending on a number of parameters and is programmatically changed. However, whenever the Userform has been shown and the user is closing the file the VBA Project Password dialogue box pops up. The password pop-up has to be cancelled 2-5 times before it disappears.
The problem is of course very annoying and difficult to explain to clients that have paid me for the solution. I have spent a lot time trying to resolve the problem, but with little success.
Recently I read in a forum that it was caused by the Acrobat PDFMaker COM Addin. I could not believe this, but when I disabled the add-in everything works fine. Also, in machines that does not have the add-in installed there are no problems reported.
How to Reproduce the BUG (5mins) (Courtesy Randall):
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.
As a workaround I have tried to disable the add-in on start-up but the line Application.
").Connect = False
will throw an error “This add-in is installed for all users on this computer and can only be connected or disconnected by an administrator.”
I have read in some forum that the problem occurs if the ActiveX DLL does not properly release the reference to the workbook that contains the password-protected VBA project (http://support.microsoft.com/kb/280454)
Any help to resolve the problem or find a workaround is much appreciated.