I thought I was being cute when I added password protection to me VBA code.

But now, I need to go in and modify all the reports created with that
worksheet. I have written the code that will search for all files containing
the VBA project that has the error in it. It deletes all the VBA code and
modules and deletes all the buttons on the report itself and deletes the
hidden sheets that the VBA used.

That leaves each file intact as a completed report, but creates what we
refer to as a "Distribution Copy" that opens without the need to
"Enable/Disable" macros. In other words, after my code cleans it up, it is a
simple workbook with the three sheets that comprise the original report that
my VBA code generated.

The latest version of this report generator will only allow the user to save
a "Distribution Copy" that strips out all the macros, buttons and extra
hidden sheets, so I expect to avoid this problem in the future.

My code to clean the old files runs automatically the first time the user
runs this new version. everything works well, except when it encounters one
of the worksheets that was built with the 2 version of my program that used
VBA protection. Then it just hangs.

I am trying to come up with a solution and have these ideas:

1) Just make a list of those files and deal with it later
2) Just delete those files
3) Just open them and delete the buttons, which is the only way the user can
access the VBA anyway

But, in order to do any of those, I need to know how to determine if the VBA
in a workbook is password protected.

Can anyone suggest solution for this? I am looking for any suggestions
about how to test the files before (or after) I open them, and possible
another solution to the three I have mentioned above. Unfortunately, I can't
go around to each user's desk and manually fix this. And, unfortunately, I
am the only person I interact with that even remotely understands what I am
trying to do. I've just been asked to make it work and a few months ago, I
knew nothing about Excel VBA.

So, ask always, I really appreciate all your help.

Ken

1. If your code "just hangs" without producing a message about the
password you might be able to use On Error.

2. A workbook which is not protected will still open if a non-existent
password is supplied. Perhaps you could put the password in your code
anyway.


--
BrianB


------------------------------------------------------------------------
BrianB's Profile: http://www.excelforum.com/member.php...info&userid=55
View this thread: http://www.excelforum.com/showthread...hreadid=386314