Consulting

Results 1 to 8 of 8

Thread: Need a VBA Ignore "Not in List" Validation Error

  1. #1
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    111
    Location

    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...
    Application.Range("E3").validation.ShowError=false
    Gary

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    Can you supply example of the code you are currently using that is erroring out?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    111
    Location
    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.




    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.

    Screenshot 2023-Error.jpg


    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
    Attached Images Attached Images

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,910
    Quote Originally Posted by garyj View Post
    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:
    2023-11-02_144317.jpg
    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?)
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    111
    Location
    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:
    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.


    Screenshot Driver Availability.jpg

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

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,910
    Have an explore of
     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.)
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,910
    Attached is my vba-free data validation where I don't get such errors.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    111
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •