Solved

Password prompt for VBA project appears after Excel quits

Posted on 2014-02-10
15
2,559 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 48

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
 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

717 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