cell validation on protected sheets

[timp]

I am trying to update cell data validation lists on a protected sheet. Using
protect/unprotect every time is cumbersome and difficult. So I am using
userinterfaceonly:=true so that the code can update protected sheets. But
this doesn't seem to allow it to update cell validation lists - I get the
familiar application-defined or object-defined error messsage. Suggestions
welcome. Thanks.

 

[Dave Peterson]

There are somethings that can't be done by code even when you use the
UserInterfaceOnly parm.

You have to unprotect, do the work, and reprotect in those cases.

And you have one of those cases.

 

[timp]

Thank you Dave - meantime I simplified my protect/unprotect puzzle (turned
off events so that my event change sub wasn't calling itself repeatedly and
trying to make changes after it had finished once and turned protection back
on). Haven't completely cracked it yet but am hopeful.