Password prompt for VBA project appears after Excel quits

I did disabling Com Add-Ins and it seems solve the problem.  But the workbook will be used by around 30 users, it will be hard to let everyone do it.  I'm looking for some easier way to fix it.  If there is no easy way to do it, then I will remove the protection.

If you don't already do it this way, try changing your protection by replacing it with a statement like this when the sheet is activated.

ActiveSheet.Protect Password:="My password", Userinterfaceonly:=True, DrawingObjects:=False

That does not address the problem. The Asker is trying to protect the code behind the workbook.

Kevin

I just removed the protection.

This is a problem that has intermittently plagued my own add-ins. While working on a specific situation for a client, I came up with a solution. 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

Select allOpen in new window

If anyone has a chance to try this, could you let me know if it helps. Feel free to email me directly, with the subject line "VB Password Prompt Issue".

jon@peltiertech.com

What workbook is being closed--your Add-In or the user's workbook?

If your Add-In, what might you be doing to set the dirty flag?

I've used this technique before but have never had to do so with an Add-In since they are opened read-only and should not be changing themselves. My case is when the workbook is being saved and I want to alter it before saving such as showing a "Macros Disabled" tab--I configure the workbook, save, restore the workbook, and set Saved to True.

Kevin

Kevin -

This is my add-in (actually my client's) that is closing, as Excel shuts down. Nothing is dirtying the add-in; if I queried ThisWorkbook.Saved, it would return True. That is what is so strange about this whole password prompt problem: no apparent (reproducible) cause, and the solutions are random and obscure.

In fact, add-ins are not opened as read-only. You can make changes to them and perform a save, just fine. But my add-ins (and my client's) don't make any changes during normal operation.

My client's add-in had the problem when the user's actions load a UserForm, then clicks a help button on the form to open a web page in a browser tab. Later if there are no open workbooks when Excel quits, the password prompt appears. This is a complicated repro path, but it replicated the problem every time on one of my machines (but not ever on another machine). The code used a simple ThisWorkbook.FollowHyperlink command to open the help file. It's a large and complex add-in, but there are no DLLs or anything.

For what part of the workbook is the password prompt? Is any part of your workbook password protected? Why? I have never password protected anything unless the client specifically asks me to do so and only for sheet protection.

Have you tried setting the read-write bit on the workbook file to read only?

Have you tried disabling other Add-Ins?

Kevin

The VB project is password protected as always. It is sold commercially, not deployed within a client's users, so we're going to make it as hard as Excel permits for a user to access the intellectual property.

In general this is not a problem, except for the less than 1% of users who encounter the glitch.

I have an opinion about password protecting projects ;-)

Basically, most users don't care about the code. And those that do can hack into it anyway.

Have you considered using Unviewable+?

http://www.spreadsheet1.com/unviewable-vba-project-app-for-excel.html

It works a lot better than Excel's lame password protection scheme.

But, back to your problem, have you tried removing all other Add-Ins? Removing the password? Write protecting the workbook file?

Kevin

Kevin -

I've been dealing with this issue since forever. I know all the usual tricks, and as usual, they didn't help.

Of course removing the password bypasses the issue, but then it's not password protected.

I use Unviewable+ on my own projects, and I'm considering removal of the password from my unviewable projects. My client, whose add-in has the current problem, has yet to switch to Unviewable+.

- Jon

If you use Unviewable+ you don't need a password. The project is completely hidden.

Kevin