Excel 97 Unprotect problem 1

[ChrisBurch]

I have a single sheet workbook, which is protected. When a button is clicked, I unprotect the sheet, do some work, then protect the sheet again.

This code works properly when I step it through in the VBA editor. However, when I run it from the button I get the following error:

Error 1004
Unprotect method of worksheet failed.

My code is:-

sub my-button ()
ActiveSheet.Unprotect "my_password"
Range("B4,B7,B13,F13,I13,A17:J44,G5,I5&quot.Select
Selection.ClearContents
yada
yada
yada
ActiveSheet.Protect "my_password"
end sub

I have also tried this with the syntax, ActiveSheet.Unprotect password:="my_password" with the same results.

Can anyone suggest a fix?

Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[xlbo]

Ho hum - you're using the CONTROLS TOOLBOX command button aren't you. Just change the "Take Focus On Click" property to false and all your problems will go away.....until the next one ;-)

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[ChrisBurch]

Thanks Geoff,

Yes I'm using Control toolbox buttons, and I'll try you solution tomorrow. Am I reading (between the lines) that your preference would be Forms Buttons??? If so, why??

Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[xlbo]

Nope - no preference for FORMS - in fact I prefer Controls toolbox items as they have more peoperties that can be controlled. The only time I use forms toolbar objects is specifically when I don't need to control much other than attaching some code. The only reason for any subtexts (real or inferred ;-) )is that this is such a common problem - it happens to virtually everyone, the 1st time they try and attach some code to a controls commandbutton.

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde