1004 Cell Protected Error for data validation lists when locked and protected

C

chris_j_adams

Hi,

With XL97 I'm wondering if it is possible to change the value of cells
with data validation lists when:
(i) those list validation cells are locked; and
(ii) the sheet is protected, with userInterfaceOnly:=True

I'd like to have a button to reset all these cells back to blank (ie.
the first item in the validation list). However, I get a 1004 error
("the cell is protected and therefore read only") when I try to do this
via VBA, like so:

Range("A1").MergeArea.Value = ""

There are quite a few validation cells, each looking at different
lists, and they are generally merged cells. Perhaps there's an
alternative way of setting the cell to the first item?

Thanks in advance if anyone can provide any help/suggestions.

Regards,
Chris Adams
 
B

Bob Phillips

Unprotect the worksheet first, do your stuff, and protect it again
afterwards.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
C

chris_j_adams

Thanks Bob. I was hoping that was not the only solution, simply because
my code requires a loop and there is potential for error, which means I
have to put an error trap in that checks for the sheet protection in
case I get flipped out. I gues it has to be done.

Many thanks for your help, much appreciated.
Chris Adams
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top