PDA

View Full Version : Sleeper: Cannot remove Open password from workbook



Jacob Hilderbrand
10-08-2004, 06:44 AM
Got a strange problem here. I cannot remove the Open password from a workbook. When I go to SaveAs and remove the password and save, the password is still there.

Windows XP and Office XP. The file is about 1.5 MB with 20+ sheets, 20+ UserForms, 10+ Modules, and 2 Class Modules. Probably over 75 subs if I had to count them all.

What's really odd is that I can change the password, and the new password is saved, but deleting the password is not saved.

I tried to remove the password on 4 PCs with no change. Any ideas on what happened?

Zack Barresse
10-08-2004, 08:26 AM
Are you getting the pop up asking to overwrite your file? You should be, then save over it.

CBrine
10-08-2004, 10:52 AM
DRJ,
The only thing I can think of that might do something like that, would be the file attributes? Perhaps you have network change rights, but not delete? Maybe excel does something close to a delete and resave to remove the password??? Haven't tested anything though.

Jacob Hilderbrand
10-08-2004, 12:24 PM
I did get a "do you want to replace" message box. I tried saving over the file and saving with a new name.

I am on a network at work, but I can delete files. Also when I try at home I get the same thing.

Also if I chang the password to say 123 when I go to look at the password it is ***. Then when I remove the password it is still 123 but displays as ************.

CBrine
10-08-2004, 12:29 PM
Weird stuff,
I tested it on my system. Win 2000 and Excel 2002. I was able to wipe the password without any problems at all. Although I had to find out where the add the password feature was, since I haven't used it since I upgraded from 97 a few months ago. :)

Jacob Hilderbrand
10-08-2004, 12:37 PM
I can remove the passwords on other workbooks so I suppose something in mine is currupted. I wonder what it is, everything else runs fine.

CBrine
10-08-2004, 01:30 PM
DRJ, Is it something you can upload? Then we can see if it might be a local enviromental setting or something on your system? I know it's shot in the dark, but you never know. Although, if it's password protected, it mostly likely is confidental information.

Jacob Hilderbrand
10-08-2004, 01:45 PM
Yeah it's confidential stuff for work. But I have tried on several PCs at work and at home and is the same. I don't see why I can change the password but not remove it.

johnske
10-08-2004, 04:26 PM
It dont do it on mine (Off 2000) but if the prob. is contained within the one book it sounds like the book has been corrupted alrite.

I would take the easy route and export all the modules & forms, import them into a new book then copy/paste all the sheets into the new book :bink:

Jacob Hilderbrand
10-12-2004, 11:04 PM
Very strange. Now I just decided to move everything to a new workbook. So I moved all the worksheets, then copied over all the modules, userforms, and class modules.

Save.

Then I open it up no problem. Initially there is no password for this workbook. Then I add a password and save. It still works fine and I can open with the password. Then I remove the password and save and the password does not go away.

johnske
10-13-2004, 12:28 AM
It must really like what you're saying to it & doesn't want you to take it back :rofl

johnske
10-13-2004, 12:58 AM
Seriously though, after looking at your new post (signature) the two may be interlinked i.e. a macro virus.... :mad:

Wot about posting it in TSG's security forum for the security experts to look at?

John :bink:

EDIT: You say your other books work OK - right?

But wot about when you put the same password in one of these (or an empty) book then remove it, is the password removed ok then?

If it removes it ok in another book it would seem to indicate the prob. lies hidden somewhere in the CONTENTS of the book and not the actual book itself.... :dunno

iwrk4dedpr
10-14-2004, 09:17 PM
Try transporting just the sheets to the new workbook. Then save it with and without the passwords, and try changing the passwords.


Then one by one import the modules and keep trying to change the password until it breaks. That should be the module page causeing you problems.


Also don't copy the workbooks using the right click on sheet tab method. If there is code on the worksheets module page they might be causing issues.


Regards,
Barry:whip

Jacob Hilderbrand
10-14-2004, 09:38 PM
EDIT: You say your other books work OK - right?

But wot about when you put the same password in one of these (or an empty) book then remove it, is the password removed ok then?

If it removes it ok in another book it would seem to indicate the prob. lies hidden somewhere in the CONTENTS of the book and not the actual book itself.... :dunno

Other workbooks work fine. I can add, change, and remove the password.

Jacob Hilderbrand
10-14-2004, 09:40 PM
Try transporting just the sheets to the new workbook. Then save it with and without the passwords, and try changing the passwords.


Then one by one import the modules and keep trying to change the password until it breaks. That should be the module page causeing you problems.


Also don't copy the workbooks using the right click on sheet tab method. If there is code on the worksheets module page they might be causing issues.


Regards,
Barry:whip
There is no code in any worksheet. All events are handled from the ThisWorkbook module. I'll try moving the modules one by one when I get some time. Unfortunately there are about 50 (userforms + modules + class modules) so it will be a pain.

iwrk4dedpr
10-14-2004, 09:41 PM
Well then just try commenting out all the code. That's easy enough. That at least could tell you it's a code issue. Or if it is a corrupt workbook.


Barry:hi:

johnske
10-14-2004, 10:26 PM
What about using the ms "find" tool on the toolbar to search the whole project for any references to your password, or, just any references to the word "password" to see if there's something hidden somewhere that's causing a conflict ?? - may be quicker... :confused: :dunno :bink:

Jacob Hilderbrand
10-14-2004, 11:21 PM
Well the password would be encrypted with the workbook and wouldn't be stored in any cell. I was thinking there might be some code conflict, but when I open the file and disable macros it is still the same thing.

I thought moving everything to a brand new workbook would do the trick, but still the same thing.

iwrk4dedpr
10-14-2004, 11:29 PM
Well you were right in trying to move everything to the new workbook.

But you'll probably need to do it in stages now.

First, recreate each worksheet in the new workbook. ( if you copy / move using the right click on the sheet tabs ) you may inadvertantly bring something over.

Second, try saving with password and then changing he password and resaving and see if the problem disappears.

Third, import the code from the old workbook to the new workbook. And with judicious use of commenting out code begin the laborious process of finding out which routine kills the ability to change the password and have it reliably saved.


Sorry bud sometimes there just isn't a simple solution.

Because of your post I'm creating a workbook replicator. What it's going to do ( at least I'm planning on it ) is to extract the code from all module pages. Create a new workbook. Then with the existing workbook recreated all aspects from named ranges to formulas to values ( I'm dreading trying to replicate a chart or pivot table ( maybe I'll just blow those off )). That way if I have to make a replicate workbook I can just click a button.



Regards,
Barry


So lets get :whip

johnske
10-14-2004, 11:48 PM
What I meant was using the "Finder" in the VB Edit window to search through the code in all the modules etc.

But If you've disabled macros - Well I just dunno now :bink:

Zack Barresse
10-14-2004, 11:50 PM
Hey Jake, have you tried it with a **** yet?

Jacob Hilderbrand
10-15-2004, 12:17 AM
I haven't tried with a ****, but the file would be encrypted and it wouldn't be easy to un-encrypt it. :eek:

I guess there is really no way to tell what is causing this without moving every sheet, module, userform, and class module to a new workbook and testing one at a time. Given the size of the workbook this is no simple undertaking. :mkay

If I can figure out the actual cause I let you guys know. Thanks everyone for all the suggestions. :)

Mike_R
10-17-2004, 06:00 PM
Wow, what a doozie... :dunno

Hmmm, yeah, the trouble could be in any one of the Worksheets, I guess. It may not be *that* simple, but assuming taht something is "attached" to one of them that is causing this, you might try a shot-gun approach:Sub Main()
Dim oSheet As Object

For Each oSheet In ThisWorkbook.Sheets
Call oSheet.SaveAs(Sheet.Name & ".xls")
Next Sheet
End Sub Then you can open them all and see which (if any) complain.

If it's all of them, then I would save a new blank Worksheet as a Workbook and attach it here so we can kick it around... and/or infect our own computerrs. :D