Removing Data Validation (Listbox)

[NoodNutt]

Hi everyone

Is there a way of removing validation from a specific cell after a value is
selected from another cell (List).

assume I have a validated "Company" list in Column A and the validated
"Employees Names" listed in column D

eg

After selecting "...Unlisted" from cell A1, can I then using VB to remove
the validation in D1 so I can manually input a name/value in it.

TIA
Mark

Also

could really use help on the following which I posted already, but as yet
have recieved no responses to, just need to know if it is possible.

Example:

If I select XYZ Trans from validated "Company" list in Column A, I would
like the validated "Employees Names" listed in column D to display only the
names of the employees for that company.

Once again
TIA
Mark.

 

[OssieMac]

When you say "remove the validation in D1 so I can manually input a
name/value in it", Do you mean that:-
You want to add a name to the validation list for D1 or
You simply want to remove the validation from D1 and type a name into D1 or
You want to type a name into D1 and add it to the validation list and then
reinstate the validation for D1 with the extra name in it.

 

[OssieMac]

Hi again Mark,

In answer to your second question; it can be done but needs an event driven
macro that runs each time the company name is changed. Also needs some
special setup of the validation lists. If you want it then let me know but it
might be a day or two before I can get back to you on it because I am
committed to other things at the moment.

 

[Roger Govier]

Hi Mark

in answer to your second question, you need Dependent Lists.
Take a look at Debra Dalgleish's site
http://www.contextures.com/xlDataVal02.html
for clear instruction on how to do this - and lots more!!

With regard to your first question, one of the problems with DV is it can be
bypassed totally if you paste a value into a DV cell as opposed to typing it
or selecting from dropdown.
You can use this to your advantage, if you type the new name somewhere else,
Copy it, then Paste it to your cell with "...Unlisted"

However, if you need the name to be available in the Source list for the
future, just add it there first, then use the dropdown to select it.
Make your DV lists dynamic, and as you add new names to the list, so they
will become available in the dropdown.

 

[NoodNutt]

Thx heaps for your help roger

Unfortunately, I work in the transport industry and most of the guy's there
are all but illiterate and would not know how to add a name to a named list,
then edit the name range to include it the droplist. Looks like I will have
to do it for them as the need arises.

I will check out Debra Dalgleish's site.

many thx

Mark.

 

[NoodNutt]

Thx for the reply OzzieMac

I will check out Roger's advise and sus out Debra Dalgleish's site.

many thx
Mark.

 

[NoodNutt]

G'day Roger

Deb Dalgliesh's handy work paid well.

Thx for poining me in the right direction

Regards
Mark.