unprotecting a worksheet

[mchugh]

hi

i'm currently working on a simple module for a spreadsheet that my father uses at work. the problem i am having is related to protection. certain columns have to be protected, so that people that use the spread sheet cant just input any old values. setting this up is fine. the problem is, that i need to edit the value of the cells in VBA. now i have tried to unrpotect the sheet in code, but it simply wont work. i'm trying to do something like this:

WorkSheets("Price Match&quot.Activate
ActiveSheet.Unprotect("password&quot

i've tried this lots of different ways round, but everyway i've tried it, i get a run time error - 1004 "unprotect method failed" or something similar

does anyone have any ideas? only its driving me crazy!

thanks in advance
Craig

 

[mchugh]

hmmm, ok, its stopped working again, but i didnt change anything :s something very odd is going on...

 

[xlbo]

You don't need to activate OR select to protect / unprotect the sheet y'know

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[mchugh]

*cries* i cant get this to work at all. when i thought it was working, i think i must have manually unprotected it, and then ran the code - so if theres no protection on the sheet, it runs ok, and it re-protects when its finished successfully too. it just doesnt seem to want to unprotect it at all - always the same error.

 

[VBAjedi]

This is going to be a long shot, but:

I had a VERY odd and unsolveable error a while back that turned out to be completely unrelated to the error message I was getting. Rob Broekhuis spotted the fact that I was using "End" instead of "Exit Sub" in a different module. Fixing that cleared up my error!

Whether or not that is your exact problem, the moral of the story is that Excel can be ridiculously sensitive to seemingly small abuses of the code. I suggest you review your code - especially modules that call or are called by another sub.

Try disabling all subs in the workbook, and running one that does nothing but unlock the sheet. If that works, start adding the other subs back in one at a time.

Hey, nobody ever said debugging was always easy!

Hope this helps somehow.

VBAjedi

 

[glenntb]

Have you tired making a copy of the "price match" sheet and running your macro against the copy?

Glenn

 

[mchugh]

i think i'll start a blank work book, and build it again from the bottom up - theres hardly any code anyway, so it wont take that long.

 

[mchugh]

hmmm, i dont suppose anyone could send me a small spreadsheet (doesnt need any data) where it unprotects a password protected sheet in code, and then reprotects it?

it would be most appreciated!

its just i just removed ALL code from the project, except those key lines, and that still broke :s

 

[xlbo]

post your email and I'll send you something

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[mchugh]

mchughcraig@hotmail.com

 

[mchugh]

hi

the problem seems to have been resolved by changing machines - i.e. the installation on the computer i was using seems to be dodgy :\ how frustrating.

thanks for all your help
Craig

(cheers for the .xls geoff)