Need a VBA Ignore "Not in List" Validation Error

Hello all...

I have a data validation dropdown created through a unique filter on another sheet. This particular dropdown is to schedule drivers for a dispatch company, and so I created a filter that removes drivers who are already on trips during that time. The dropdown checks the dates for the trip and looks at only available drivers.

It works awesome, except that it presents a problem. When selecting the driver that is available, that driver is immediately removed from the list by the filters, and the data validation error display for the cell. The item is no longer in the list.

I would like to write code to mimic the user clicking the ignore error on that cell, but have not been successful in finding out how. I think I just don't have the language on these types of errors by which to search Google. Can anyone help?

I tried a bunch of things, the last being this... which didn't work...

Code:

---

Application.Range("E3").validation.ShowError=false

---

Gary

Can you supply example of the code you are currently using that is erroring out?

Hello Aussiebear..

There really isn't any code... as it's all done by dynamic filters etc. Here is a snippet showing what I am doing, and what code there is.


Attachment 31168

What you don't see here is the Bookings, where drivers are selected for trips using a drop down menu that is populated through data validation list referencing K4# above.
When the dispatch clicks on that dropdown to select a driver, the code takes that trip's bus leave time and return time and puts them into G1 and G2 respectively.
The list is looking for data spilled to column K, and that information as you can see comes from the other 4 columns.

But as soon as Jenny is selected on Bookings, the error shows up... like this... and the reason is because the selection causes Jenny to be listed in Col G above, which removes her from Col K, which removes her from the Dropdown list, and data validation says no.

Attachment 31169
http://www.vbaexpress.com/forum/imag...BJRU5ErkJggg==

I had thought a nice fix would be for VBA to select the Ignore Error in that triangle dropdown above. That was the reason for the post.

But another possible way would be to rewrite the formulas on the first sheet so that her name is either not excluded by the active dropdown (not sure how) but it would cause her name not to show up in Col G, which would allow it to stay in Col K, and thus there would be no error. OR else there may be a way to add the active dropdown name back to column K.

Thanks for any help.

Gary

Quote:

---

Originally Posted by garyj

and the data validation error display for the cell. The item is no longer in the list.

---

I'm not so sure that is a data validation error directly, it looks like Excel's more general error checking; you should be able to click the dropdown of that warning and choose Ignore error:
Attachment 31176
You might be able to select multiple cells and do that.
Whether they'll stay ignored when you make another choice or re-open the workbok, I'm not sure, because I'm unable to reproduce your situation here (perhaps attach a bare bones workbook demonstrating the problem?)

Thanks, p45cal. When clicking that yellow error it says that the item is not in the list. It was in the list before it was selected, and not after it was selected. It is removed because of how my dynamic lists are created. I want only the drivers available for that date, so it goes through the spreadsheet to locate all drivers on trips during that date and lists them as unavailable, and then the filter removes those from the all drivers list and presents the remaining. So it makes sense that the list no longer has that driver available.

I could just have the user click the Ignore Error, and that suggestion works. My problem with it is that the main function of the spreadsheet is to connect available drivers to available buses during certain periods of time. Even with a dozen trips a day, the ignore error requires a click in the cell, a click for the drop down, and a click for Ignore. My original post was to find a way to do this programmatically after the selection is made...thus removing the issue from the user's view.

However, I found one solution... after considering last night that I could add the name back in.

A. I used a Worksheet_Change event to follow driver selection using:

Code:

---

If Target.Address = "$E$" & ActiveCell.Row Then

---

B. The "Then" adds the selected name to a new column shown below in "Less Selected"

Sheet Below Explained

1. Drivers Out: worksheet function generated list of all drivers on trips during any part of the time span in the active row
2. Less Selected: VBA generated (See A-B above)
3. Drivers Out 2: worksheet function generated list which removes Less Selected from Drivers Out and shows the remainder (in this case nothing)
4. Drivers Off: worksheet function generated list of all drivers booked off for holidays etc..
5. Not Avail: worksheet function generated list using Vstack of Drivers Out 2 and Drivers Off
6. All Drivers: worksheet function generated list of all drivers
7. Drivers Avail: worksheet function generated list of All Drivers less Not Avail.

This works. When I select a driver now, no error comes up. And now when I open the dropdown to view the list of drivers, the selected driver is still in the list.
And in case any of you wondered about this, I am using the same solution for the list of buses.

http://www.vbaexpress.com/forum/imag...AAAElFTkSuQmCC
Attachment 31178

Thanks all for your ideas, and all you do to make this site worthwhile.
Gary

Have an explore of

Code:

---

Application.ErrorCheckingOptions.ListDataValidation = False

---

It applies to the entire Excel Application, so if you want to preserve it's TRUE status for elsewhere you could put that line in the Worksheet_Activate() event and set it back to TRUE in the sheet's Worksheet_Deactivate() event. (I still can't test if this will work since I can't get such an error to appear here.)

Attached is my vba-free data validation where I don't get such errors.

Strange, couldn't get your suggested VBA function to make a difference; though I tried to recreate the issue rather than mess up my fix on the particular issue. I caused an error, but it was always a popup box rather than a cell triangle warning, and the popup wouldn't allow me to put in the wrong data.

Your code in vbaExpress71200.xlsx is the same as mine, other than mine uses dynamic lists so that the data keeps spilling. I don't know if that makes a difference.
In my columns when I type a name into one list, the name comes off the other immediately, much the same as your data dropdowns work. My dropdowns are on another sheet of course, and the filter brings the data to this hidden sheet. The rows are not necessarily contiguous on the other sheet. I noticed that while there are workarounds for filtering a range of columns that are not contiguous, dynamic filtering prefers the ease of contiguous columns. I wonder if breaking contiguity in rows can cause the inner workings to perform differently.

I have lots of wondering... but no time to figure each one out. For now, I press forward. This certainly has been an interesting thread. Thanks

Gary