Protecting worksheet

[amit.vasu]

Hi

I was doing some VBA programming in Excel. I wanted to protect the
worksheet and in order to do so I write the code below.

*****************
For Each w In ActiveWorkbook.Worksheets
AllowSorting:=True, AllowFiltering:=True
w.Protect Password = "password"
Next
******************************************

In my workbook, I had 4 worksheet. The code above worked well for three
worksheet except the worksheet 2. It was unprotected even after
running the code above.

So I write down the following code right after the above loop.

*****************************************************
Worksheets(2).Activate
Worksheets(2).Protect Password = "password"
***************************************************************************

and again at the end I wrote the following code.

******************************************************
ActiveWorkbook.Worksheets(2).Protect Password = "password"
******************************************************


Now my problem is I can not unprotect that worksheet. When I go to
tools --> Protection --> unprotect worksheet and enter the above
password ("password") it does not work.

It give me the dialogue box saying

"The password you supplied is not correct. Verify that the caps lock
key is not......"

I would highly appreciate if someone could tell me what is wrong in
above code and what password will work to unlock the worksheet.

I can not use the free tool as client is not ready to use any other
tool. They just want the solution to unlock the worksheet by themself
without using any other tool.

Please help.

 

[Guest]

you had an error in your code.

ActiveWorkbook.Worksheets(2).Protect Password = "password"

should have been

ActiveWorkbook.Worksheets(2).Protect Password:="password"


I suspect the password is False

if that doesn't work, then try unprotecting it in code with the same mistake

ActiveWorkbook.Worksheets(2).UnProtect Password = "password"

 

[amit.vasu]

Thank you very much for your quick reply.

I never realize that there is a mistake in my code and you are right.

I tried using password "False" but that did not work.

But I used the following code

ActiveWorkbook.Worksheets(2).UnProtect Password = "password"

and it worked like a champ.

It is really good to know what was the problem.

My problem is client have 10 different copies of the workbook with
different information and all workbooks are locked.

Is there any way to know what would be the password ?

Many thanks Tom for your help.

Regards

 

[Guest]

depends on what you mean by locked. If you can open the workbook in Excel
and you mean the sheet is protected and/or the workbook structure/windows are
protected, then you can use this:

http://www.mcgimpsey.com/excel/removepwords.html

If you mean you try to open the workbook and it prompts for a password and
then you can't open it, then you are looking for a more sophisticated
password cracker. I believe most of these are commercial products with a
cost. Such as

http://www.lostpassword.com

but do a google search and see what you can find.

 

[amit.vasu]

Tom

I can open the workbook but can not edit the worksheet.

I will look into the first option you provided.

I am googling on this issue. Knowing the password will make my life
easier compare to removing the security on worksheet.

Many thanks for your help.

Best Regards

Amit

 

[amit.vasu]

Tom

The first link you provided helped me.

You made my life easier.

Many Thanks

Regards

Amit