How do I unprotect a sheet within a macro?

[Mick]

Please can anyone advise how I can overcome this Run-time error when using
the code
below:

"Unprotect method of worksheet class failed"

Worksheets("Template").Protect Password:="MyPassword"


Or what code should I use to remove a password format a sheet using another
macro and put the same password back on again.

 

[Sharad Naik]

The code used is correct.
(Same format for Protect and Unprotect.)

Are you sure that the password you entered in the code is correct?

Regarding your second query, it is not clear what you mean. Please note
you must "KNOW" the password. You can not get what password is set for
a worksheet, through the code.

Sharad

 

[Mike Fogleman]

Worksheets("Template").Unprotect Password:="MyPassword"
Worksheets("Template").Protect Password:="MyPassword"

Mike F

 

[Mick]

Mike

I have been able to test this on a new sheet and can get it to work.
However my problem is that I have a template that I am using. If I open the
template and run the macro I get the error message and likewise if I open a
new workbook from it I get the same. But if I step through the macro it
works OK. Does that make sense, it doesn't to me?

Regards

Mick

 

[Dave Peterson]

Are you using xl97?

Are you trying to run your code from a commandbutton from the control toolbox
toolbar placed on a worksheet?

If yes, try changing the .takefocusonclick property to false for that
commandbutton.

If the control you're using doesn't have that property (but it's still from the
control toolbox toolbar), add a line to the top of your procedure.

activecell.activate

There's a bug in xl97 that was fixed in xl2k.