Check to see if exists and error message

  • Hi All,

    Any ideas how to check the contents of range A2:A12 against a list of non allowed modules and throw an error message if the data validation is not correct?


    Cell A2 = TR002
    Cell A3 = TR022
    Cell A4 = TR045
    Cell A5 = TR017
    Cell A6 = TR051
    Cell A7 = TR066
    Cell A8 = TR089
    Cell A9 = TR092
    Cell A10 = TR113
    Cell A11 = TR121
    Cell A12 = TR145

    Column A selections have the limitations listed below and should throw an error i.e "Invalid Selection" [SIZE=13px]where an incorrect choice is made[/SIZE]

    [SIZE=13px]List of exclusions.[/SIZE]

    If [SIZE=13px]Module[/SIZE] TR001 is Chosen, Module 015 or Module 021 cannot be chosen
    If [SIZE=13px]Module[/SIZE] TR017 is Chosen, Modules 049 cannot be chosen
    If [SIZE=13px]Module [/SIZE]TR045 is Chosen, Modules 046 cannot be chosen
    If [SIZE=13px]Module[/SIZE]TR089 is Chosen, Modules 095 to Module 113 cannot be chosen
    If [SIZE=13px]Module[/SIZE]TR051 is Chosen, Modules 058 to Module 066 cannot be chosen
    If [SIZE=13px]Module[/SIZE]TR022 is Chosen, Modules 0145 or Module 025 cannot be chosen
    Only one Exam from TR090 and TR091 may be chosen

    Error messages should be shown in corresponding cells to the right D2:D12

    [SIZE=13px]No idea where to start on this so I will need some extensive guidance.


  • Hi

    Thanks for the response.

    Sample file attached.
    Basically, I want to have a student select the 10 Modules from the drop down menus, and have the data checked against the list of exclusions detailed on the work sheet

    Sample error comments have been added Good / Bad and the rationale for the comment is listed to the side.
    I would prefer if this code checks after every selection is made to prevent getting to the end of the process and finding out the errors.

    hope this helps


  • I think I see what you're asking now, but do you want the options removed from the lists or a message to appear if the wrong option is selected?

    What should happen if they choose 15 first and then choose 1?

  • Hi and thanks for the efforts so far.

    I need an error message to be shown in either direction. if 1 is selected first or if 15 is selected first
    So upon reflection, I guess this would be best for this to check the whole selections once they have all ben made rather than line by line.

    that way both the 1 and 15 would have an error message !

    Thanks again

  • Try this. I have converted the second sheet to a table and added the exclusions in column 2 onwards. I've not added them all so you'll have to do that (you'll also have to make them reciprocal if you understand what I mean). An advantage of this is that it will expand if you add new entries and the data validation can run off the first column.

    I've also output the errors in a message box. If you don't want then just remove the lines referring to "s".

  • Hi StephenR

    I have just downloaded the sheet and will go through its usage to check all parameters work as expected.

    Many thanks for you efforts so far and I will feedback tomorrow.

  • Hi

    I have tested out the proposal in depth and everything seems to works as expected.
    Great work.

    Looking at the code, the exams selected range is fixed. As there may be variations in total exam numbers selected, is it possible to have a pop up box "OnSheetactivate" asking for input of total exams to choose?
    Also, can a message be displayed if there are no errors found? "All selections are valid"
    and finally, can there be a space line between each error in the message box just for clarrity?

    Awsome work on this and very much appreicated.

    Many Many Thanks

  • Amended code above. Not sure what you mean by the total number of exams. Currently the validation list is based on the first column of the table in the second sheet. Are you saying you might only want to select a sub-set of these?

  • Hi and again, thanks you for the help and response.

    The current range only covers 12 exams as per my first request.
    Further thinking shows a flaw in my request as there may be times when only 6 exams may be needed or upto 15

    I wondered if this could be coded in as a fallback just in case.


  • It can be done, but I'm not sure how you want to handle it. You can amend the table (add or remove rows) or change the validation source, but more detail is needed on how it should be done.

  • Hi

    I understand and thank you for the explanation.

    Is it possible to have an input cell, say (C1) which would be the number of exams required (8-15), then make the range (A2:A17) dynamic, so that the code initialy only checks to see if the number of exams input matches the value in cell C1 before checking for the criteria match?

    Otherwise this piece of work is perfect.

  • Hi, I thought I would try to make this a liitle more clear.

    Rather than set a validation source range: A2:A12. is it possible to count the number of exams input from A2 downwards then check the cell C2 for an input value i.e 14 and match that to the count of range A2:A?

    If they do not match "Error" or "Valid"if they do match. Once this is completed, move into the code you have already provided to check the validty of selections.


  • Will you always be selecting 10 modules on Sheet1 though? Or does that vary as well as the number of exams stored on the other sheet?

    I think what will need to do is update the source list every time C1 is changed using a change event so that the selections are made from the right list.

  • Hi

    all options on the modules sheet are the same.
    Sheet 1 will vary from (8) to (n)

    My thought process was to have the 2 values (CountA of column A) checked against the input in cell C1

    So, CountA column A = 14 and C1=15 - "this would throw an error"


  • No worries, I appreciate the help.

    I did have a go with something along the lines of the following to no avail...

    [align=left][COLOR=#252C2F][FONT=Courier][SIZE=12px]If WorksheetFunction.CountA(r1) = Sheets("Sheet1").Range("C1").Count Then[/SIZE][/FONT][/COLOR][/align]


  • Try the following. It deletes validation and then adds data validation back for the number of cells indicated in C1 of sheet1. I've added a worksheet change code which needs to go in the sheet module - right click Sheet1 tab, View Code and paste.

    The rest in a normal module:

  • Hi And thank you for all your help.
    I have enough here I think and must stop perstering you.

    Your have been most informing with your proposals.

    Many thanks

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!