Unprotect worksheet error

[Guest]

Hi All,

I have a worksheet which is protected when the user first opens it, then
when they press a button the macro unprotects the sheet, does some things,
then re-protects the sheet. The protection is there simply to stop people
accidentally mucking around with formulas etc so it doesn't matter that there
is no password.

I am almost finished developing this and have struck a snag - the unprotect
function sends up an error when I press the macro button. If the sheet is
unprotected when I push the button everything works fine, if it is protected
I get the error message and using debug points me to the unprotect piece of
code.

I have tried;

ActiveSheet.Unprotect - didn't work

Worksheets("Sheet1").Activate
ActiveSheet.Unprotect - didn't work

Has anyone got any other ideas? Or even know why this might happen? Could
it be that the code was originally written in a new version of excel and I'm
using 97?

The error message is;

Runtime Error "1004" Unprotect method of worksheet class failed

Any help would be most appreciated.

Fiona

 

[Tom Ogilvy]

It is a bug in Excel 97. Change the TakeFocusOnClick property of the
commandbutton to false and it should work.

 

[Guest]

Thanks Tom,

That worked perfectly. Will the end users (who knows a little of VBA) have
to change the property back? They are overseas so I need to tell them when I
send it to them.

Fiona

 

[Tom Ogilvy]

Once set, it should remain set.