Worksheet_change event add / remove validation

**tone640** · 02-29-2012, 07:24 AM

In the attached, if a user puts a number in the columns 'Sick', or 'Susp'd / Leaver', validation dropdown lists are added to the corresponding cell in the 'Comments' column.

If entries are deleted, the validation is also deleted from the relevant comments column (usually).

This works to a point, but if you enter a value without hitting enter (e.g. Press tab or a cursor other than down, or click on a different cell), the validation does not get applied.

Also, if you delete entries one at a time from the bottom of several consecutive ones, the delete validation does not work.

The problems arise when testing whether the Target is blank - it will be in a different position depending on how the data has been entered.

Thanks in advance for any help you can offer - it will be greatly appreciated.

**JBeaucaire** · 02-29-2012, 11:39 AM

1) Remove the worksheet_change event for a moment from that sheet.

2) Put the number 1 in F9 temporarily

3) Select AJ9:AJ101

3) Apply this DV setting manually:

Allow: List
Source: =IF(F9>0,sick,IF(J9>0,Leaver))

4) Now look at the drop down in AJ9, it is for the SICK option.

5) Remove the 1 in F9 and put a 1 in J9.... now look?

6) Now remove the 1 altogether and look again...

No VBA needed for this.

**tone640** · 03-02-2012, 06:13 AM

Thanks for your reply.

I was hoping to avoid this route because the column also needs to allow free text - is there a way to do that as well?

**JBeaucaire** · 03-02-2012, 12:35 PM

In the DV settings, go to the ALERT tab and turn them off. This will allow you to free text as well.

**tone640** · 03-05-2012, 09:38 AM

Nice one - so simple!

It won't let me tip your scales again (not that need more rep points!), so thanks again.

One more thing, is there a way to use a list on a different sheet? It seems that I can't use this method if the list is on another sheet.

**OnErrorGoto0** · 03-05-2012, 10:29 AM

As long as you use a named range, not a range address, it can be on any sheet.

**JBeaucaire** · 03-05-2012, 02:11 PM

What OnGotoError0, said... named ranges make DV lists work on any sheet.

If that takes care of your original need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.