Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List, Data Validation, unlocked cell, protected sheet..... | New Users to Excel | |||
Why am I able to type in a cell that is locked and protected? | Excel Worksheet Functions | |||
run time error 1004 on protected worksheet when I try to run macro | Excel Programming | |||
Error 1004 when running on a protected sheet | Excel Programming | |||
validation lists and protected work sheets | Excel Programming |