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

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

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

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)

wrote in message
oups.com...
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

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

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