Conditional Cell Lock

[James8309]

Hi everyone,

I am just having problems with people editing the cell values. I know
how to lock particular cells in a worksheet but how do I achieve
conditional cell lock?

i.e People put inputs in column A and normally Column G:H are locked
for editing. However if input in column A is "ABC" I want to let them
edit or put inputs in Column G:H for that row.

e.g. If A10 = "ABC" then Unlock G10 & H10 and if cell value is other
than "ABC" then lock.

Thank you for your help in advance.


Regards,

James

 

[Joerg Mochikun]

To "lock" G10, you could use data validation: menu Data->Validation
Criteria:Custom, Formula: =A10="ABC"
Uncheck "Ignore Blanks"

Now users can enter data into G10 only if A1 contains 'ABC' (or 'abc' or
'aBc' ...text is not case sensitive). If you want to make it case
sensitive, use the formula =EXACT("ABC",A10)

Please note that users can still delete values in G10 or input rubbish into
G10 and then change A10, which would leave you with inconsistent data. For
more protection you would need a macro to monitor your input,
protect/unprotect the worksheet, lock/unlock cells to be protected and
handle cases where users want to correct their input. Can be done, but much
more complex.

Cheers,

Joerg Mochikun