Solved

Password prompt for VBA project appears after Excel quits

Posted on 2014-02-10
15
2,032 Views
Last Modified: 2016-08-19
When I added a password to VBA project, I kept got password prompt after Excel quits.  I think that may related to ActiveX in the code.  Can I add something into "Private Sub Workbook_BeforeClose(Cancel As Boolean)" to viod this?

Thanks.
0
Comment
Question by:jjxia2001
  • 8
  • 4
  • 2
  • +1
15 Comments
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 500 total points
ID: 39848038
This problem is usually caused by com Add-Ins. Do you have any installed? If so, try disabling one or more to see if the problem goes away.

Kevin
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 500 total points
ID: 39848052
Also consider that protecting your VBA code with a password is really not worth the hassle it can cause. Most people really don't care what's under the hood. Most of the few who do just want to see how the workbook was built - nothing malicious - just curiosity. The very few who would do something bad are going to do it no matter what is done to the workbook to protect the contents and that's what lawyers are for.

For what it's worth, I've been building workbooks for clients and general public consumption for years and I've never password protected anything unless the client insists I do so which is extremely rare after I make the simple argument above.

Kevin
0
 

Author Comment

by:jjxia2001
ID: 39848170
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.
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 39848241
Other than disabling the Add-Ins there is no known workaround.

Again, I urge you to consider not protecting the project. A determined hacker will get in anyway and everyone else doesn't care. I even present an easy way to do so on my website for 2003 and earlier workbooks:

www.zorvek.com/excel/removing-an-excel-workbook-vba-password.htm

Kevin
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39854104
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
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39854108
That does not address the problem. The Asker is trying to protect the code behind the workbook.

Kevin
0
 

Author Closing Comment

by:jjxia2001
ID: 39860354
I just removed the protection.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 1

Expert Comment

by:Jon_Peltier
ID: 41761853
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

Open 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
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 41761875
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
0
 
LVL 1

Expert Comment

by:Jon_Peltier
ID: 41762624
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.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 41762835
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
0
 
LVL 1

Expert Comment

by:Jon_Peltier
ID: 41762925
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.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 41763118
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
0
 
LVL 1

Expert Comment

by:Jon_Peltier
ID: 41763189
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
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 41763191
If you use Unviewable+ you don't need a password. The project is completely hidden.

Kevin
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now