Unprotecting a worksheet 2

[Spon349]

I'm trying to unprotect an Excel worksheet from within a macro (when the sheet has previously been protected with a password), but I keep getting the following error message:

"Unprotect method of worksheet class failed"

The macro executes from a button placed within the sheet I'm trying to unprotect. I've tried unprotecting the sheet directly with

Sheets("SheetName").Unprotect Password:="Password"

and also by activating the sheet and then using

ActiveSheet.Unprotect

, but neither works. I've also tried protecting the sheet with no password and then using Unprotect, but the same error happens. What could be causing this? Protecting the sheet from within the macro works fine, as does protecting/unprotecting workbooks.

Thanks

 

[Guest_imported]

Dear Spon,
Actually you can not do the same. because when sheet is protected with any password then its other contents including passwords are protected and so if you are opening it any text editor then it will show junk. This is done at apllication software level, otherwise anyone can get the password ! ! And if you have macro editing knowledge then, you can put macro-virus. so even with Command you can not do the same..
Any doubt, please revert.

Hiren

 

[DarkSun]

Spon349,
It seems you can't unprotect the Sheet you are currently on from within VB.

You can however unprotect a sheet that is not selected. Don't ask me why.

This means that if you are on sheet1 you can unprotect sheet2.

 

[DarkSun]

Spon349,

The only reason I can think of is you are trying to protect/unprotect something while it is in use.

The code for a button is running therefore you can't protect/unprotect the data until the code has finished.

 

[Kaah]

maybe the cause of all this is the old bug (by design?) where you cant access objects on the worksheet just after the user has clicked the button, because in fact the button is the active object and not the sheet? You know what I'm talking about darksun?

What I think you need to do is to activate one of the cells on the sheet before you try to unprotect anything. A good line is selection.activate
ie.

selection.activate
activesheet.unprotect

this will ensure that the button is not the active object and will allow you to start interfacing with worksheet objects again....

see how you go,, I might be wrong, its naught more than a hunch...

I would normally put in selection.activate at the start of any onbuttonclick event anyway, it saves hassles...

K.

 

[DarkSun]

Nice one Kaah!