Removing a password from a worksheet

funkyfido · #1 11-21-2013, 03:17 AM

This is the code I have changed to but still getting problems.

Code:

Sub test()
Workbooks.Open Filename:="c:\My Documents\Try this.xlsm", Password:="one", WriteResPassword:="two"
ChDir "c:\New\"
ActiveWorkbook.SaveAs Filename:= _
"C:\My Documents\New\Try this 1.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Sheets("sheet1").Activate
ActiveSheet.Unprotect Password:="Test"
End Sub

BobBridges · #2 11-21-2013, 07:25 AM

Two requests, funky:

1) In general, when you say things like "didn't work", "no joy" and "still getting problems" you leave us without clues. The first time was better; you said "I get a subscript out of range message". Now maybe the subsequent three times you meant "I get the same message", but speaking for myself I'd much prefer too much detail to too little. Something like this: "It displayed the message 'Subscript out of range', and when I hit the Debug button it showed the following statement...." (so that we know not only the exact text of the message but also which statement caused it). I recommend you make that kind of problem reporting a general habit.

2) I cut and pasted your first program into a workbook, then created a protected workbook with a password-protected sheet and ran your program on it (substituting only my path for "New"), and I didn't run into your problem, so I guess my theory was wrong. Can you post the workbook itself—preferably both of them? I may as well try it with the original; if I can't reproduce the problem then, we may be stymied.

funkyfido · #3 11-21-2013, 08:10 AM

Quote:

Originally Posted by BobBridges

Two requests, funky:

1) In general, when you say things like "didn't work", "no joy" and "still getting problems" you leave us without clues. The first time was better; you said "I get a subscript out of range message". Now maybe the subsequent three times you meant "I get the same message", but speaking for myself I'd much prefer too much detail to too little. Something like this: "It displayed the message 'Subscript out of range', and when I hit the Debug button it showed the following statement...." (so that we know not only the exact text of the message but also which statement caused it). I recommend you make that kind of problem reporting a general habit.

2) I cut and pasted your first program into a workbook, then created a protected workbook with a password-protected sheet and ran your program on it (substituting only my path for "New"), and I didn't run into your problem, so I guess my theory was wrong. Can you post the workbook itself—preferably both of them? I may as well try it with the original; if I can't reproduce the problem then, we may be stymied.

Sorry if I didn't reply correctly, not trying to upset you.
I have attached the files which I used. Test.xlsm contains the macro to open try this.xlsx and rename it and unlock the cells.
The error which I receive is Microsoft Visual Basic box with just 400

BobBridges · #4 11-22-2013, 11:29 AM

Oh, don't worry about upsetting me, funky. Some people are just too eager to get upset. I'm just saying that it'll help us help you if you keep both those in mind.

I'll check out those two workbooks and see what I can see. Sorry for the delay; busy week.

BobBridges · #5 11-22-2013, 12:26 PM

It's probably not related to your problem, but when I try to run this I get a problem in a different place. You're opening [Try this.xlsm], but when I download it and save it, its name is [Try this.xlsx].

I tried renaming the downloaded workbook as [Try this.xlsm], but Excel balked; it says "Run-time error '1004' / Application-defined or object-defined error". So I tried again by changing the name in the program to [Try this.xlsx]. That opened the workbook just fine, and then consented to save it as [Try this 1.xlsm]. But I didn't get your problem with the last statement, either.

Before I give up, let me try asking questions from another angle: Where are you when you run the "test" macro, and what process do you use to invoke it? Which workbook and sheet is active, what keystrokes or clicks do you use, etc?

Oh, and let's have a better description of the error, too. "Microsoft Visual Basic box with just 400" doesn't convey the right picture to me.

funkyfido · #6 11-25-2013, 09:19 AM

Quote:

Originally Posted by BobBridges

It's probably not related to your problem, but when I try to run this I get a problem in a different place. You're opening [Try this.xlsm], but when I download it and save it, its name is [Try this.xlsx].

I tried renaming the downloaded workbook as [Try this.xlsm], but Excel balked; it says "Run-time error '1004' / Application-defined or object-defined error". So I tried again by changing the name in the program to [Try this.xlsx]. That opened the workbook just fine, and then consented to save it as [Try this 1.xlsm]. But I didn't get your problem with the last statement, either.

Before I give up, let me try asking questions from another angle: Where are you when you run the "test" macro, and what process do you use to invoke it? Which workbook and sheet is active, what keystrokes or clicks do you use, etc?

Oh, and let's have a better description of the error, too. "Microsoft Visual Basic box with just 400" doesn't convey the right picture to me.

I am using the test.xlsm document to open the file try this. It then saves it in a new directory and tries to remove the sheet password to perform edits. I have attached a screenshot of the error. Nothing more that i stated i'm afraid.

BobBridges · #7 11-26-2013, 05:59 PM

Wow, you weren't kidding! I assumed there was text along with that error.

I did a little googling and found these references. Maybe one will help:

http://www.add-ins.com/support/micro...-error-400.htm

http://social.msdn.microsoft.com/For...p?forum=isvvba

http://www.excelforum.com/excel-prog...-400-mean.html

http://excel.questionfor.info/q_micr...el_141010.html (this one has a list of a dozen or so links to similar-sounding questions)

I didn't read them all carefully (just enough to see that there were some suggestions in each about what to do), but I notice that at least two say it may have to do with an add-in that needs to be fixed, perhaps uninstalled and reinstalled. That would go along with the fact that two of us couldn't reproduce the error on our own machines.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Password protected file no longer offers password dialog on Open	htaylor	Word	0	10-15-2013 12:35 PM
How to summarise different worksheet to a summary worksheet	samkiewhock	Excel	1	09-06-2012 03:34 AM
Outlook requests password afeter changing windows password	pask	Outlook	5	03-16-2012 08:43 AM
Removing password protection	HantsDave	Word VBA	9	03-08-2012 11:57 AM
Trouble Removing Password	cure4glass	Word	1	02-17-2012 07:19 PM