protect a cell based on values in range

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,

Hello JeKaRe,

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

Sincerely,
Leith Ross

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.


"Leith Ross" wrote:

>
> Hello JeKaRe,
>
> Data validation is a convenient tool but lacks the protrection feature
> you want. To prevent the user from changing the list once the have made
> a selection would involve writing a VBA macro. To write the macro would
> require more details about how your application is setup.
>
> Sincerely,
> Leith Ross
>
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=494264
>
>