protect a cell based on values in range

[Guest]

Hi All,

As a new excel-writer I try to do the following, so far without success.

A cell B2 uses a validation list. A range of cells uses another
validationlist depending on the value of B2.

Let's say in B2 I can choose from a list of states and in the cells A4:A30 I
can choose from a list of cities in that state.

This is working alright, but how do I protect B2 from changing once a city
has been chosen?

In my actual application there are a few columns depending on the value in
B2 so I don't want it to change once these columns have been used.

TIA,

 

[Leith Ross]

Hello JeKaRe,

Data validation is a convenient tool but lacks the protrection featur
you want. To prevent the user from changing the list once the have mad
a selection would involve writing a VBA macro. To write the macro woul
require more details about how your application is setup.

Sincerely,
Leith Ros

 

[Guest]

Hi Leith,

Your answer already gave me an idea in the right direction and i found a
solution.
(Perhaps i had been staring at the problem too long to think straight)

I wrote a function to check if the ranges have any data in them and based on
the return value I then lock or unlock the B2-cell.
Works like a charm.

Anyway, thanks for your input.

Season greetings and best wishes...
Jack.