Check to see if exists and error message

jl2509 · Nov 6th 2018

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?

i.e

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.

Thanks[/SIZE]

**StephenR** · Nov 6th 2018

Not sure what you're asking. Can you post a workbook?

jl2509 · Nov 6th 2018

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

Thanks

**StephenR** · Nov 6th 2018

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?

jl2509 · Nov 7th 2018

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

**StephenR** · Nov 8th 2018

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".

Code

Sub x()


Dim r As Range, r1 As Range, n As Long, t As ListObject, c As Long, s As String


Set r1 = Sheets("Sheet1").Range("A2:A12")
Set t = Sheets("Modules").ListObjects("Table1")


If WorksheetFunction.CountA(r1) = r1.Count Then 'all completed
    For Each r In r1
        n = Application.Match(r, t.DataBodyRange.Columns(1), 0)
        c = 2
        Do While Len(t.DataBodyRange(n, c)) > 0
            If WorksheetFunction.CountIf(r1, t.DataBodyRange(n, c)) > 0 Then
                s = s & r & " and " & t.DataBodyRange(n, c) & " cannot both be selected." & vbCr
                r.Offset(, 1).Value = r & " and " & t.DataBodyRange(n, c) & " cannot both be selected."
            End If
            c = c + 1
        Loop
    Next r
Else
    MsgBox "Complete all boxes first"
End If


If Len(s) > 0 Then MsgBox s


End Sub

Display More

jl2509 · Nov 8th 2018

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.

jl2509 · Nov 9th 2018

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

**StephenR** · Nov 9th 2018

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?

Code

Sub x()


Dim r As Range, r1 As Range, n As Long, t As ListObject, c As Long, s As String


Set r1 = Sheets("Sheet1").Range("A2:A12")
Set t = Sheets("Modules").ListObjects("Table1")
r1.Offset(, 1).ClearContents


If WorksheetFunction.CountA(r1) = r1.Count Then 'all completed
    For Each r In r1
        n = Application.Match(r, t.DataBodyRange.Columns(1), 0)
        c = 2
        Do While Len(t.DataBodyRange(n, c)) > 0
            If WorksheetFunction.CountIf(r1, t.DataBodyRange(n, c)) > 0 Then
                s = s & r & " and " & t.DataBodyRange(n, c) & " cannot both be selected." & vbCr & vbCr
                r.Offset(, 1).Value = r & " and " & t.DataBodyRange(n, c) & " cannot both be selected."
            End If
            c = c + 1
        Loop
    Next r
Else
    MsgBox "Complete all boxes first"
    Exit Sub
End If


If Len(s) > 0 Then
    MsgBox s
Else
    MsgBox "All selections are valid"
End If


End Sub

Display More

jl2509 · Nov 9th 2018

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.

Thanks

**StephenR** · Nov 9th 2018

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.

jl2509 · Nov 10th 2018

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.

jl2509 · Nov 11th 2018

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.

Thanks

**StephenR** · Nov 12th 2018

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.

jl2509 · Nov 12th 2018

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"

Thanks

**StephenR** · Nov 12th 2018

OK I misunderstood, it's only the first sheet which will vary. I'll take a look in a bit.

jl2509 · Nov 12th 2018

No worries, I appreciate the help.

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

Code

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

Thanks

**StephenR** · Nov 12th 2018

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.

Code

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$C$1" Then
    With Sheets("Sheet1")
        Set r1 = .Range("A2").Resize(.Range("C1").Value)
    End With
    Range("A2:A100").Clear
    r1.Validation.Add xlValidateList, Formula1:="=INDIRECT(""Table1[Column1]"")"
End If


End Sub

Display More

The rest in a normal module:

Code

Public r1 As Range


Sub x()


Dim r As Range, n As Long, c As Long, s As String, t As ListObject


With Sheets("Sheet1")
    .Range("A2:A100").Clear
    Set r1 = .Range("A2").Resize(.Range("C1").Value)
End With
r1.Validation.Add xlValidateList, Formula1:="=INDIRECT(""Table1[Column1]"")"
Set t = Sheets("Modules").ListObjects("Table1")
r1.Offset(, 1).ClearContents


If WorksheetFunction.CountA(r1) = r1.Count Then 'all completed
    For Each r In r1
        n = Application.Match(r, t.DataBodyRange.Columns(1), 0)
        c = 2
        Do While Len(t.DataBodyRange(n, c)) > 0
            If WorksheetFunction.CountIf(r1, t.DataBodyRange(n, c)) > 0 Then
                s = s & r & " and " & t.DataBodyRange(n, c) & " cannot both be selected." & vbCr & vbCr
                r.Offset(, 1).Value = r & " and " & t.DataBodyRange(n, c) & " cannot both be selected."
            End If
            c = c + 1
        Loop
    Next r
Else
    MsgBox "Complete all boxes first"
    Exit Sub
End If


If Len(s) > 0 Then
    MsgBox s
Else
    MsgBox "All selections are valid"
End If


End Sub

Display More

jl2509 · Nov 12th 2018

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

Check to see if exists and error message

Participate now!

Share