How can I demonstrate that a user data validation selection clashes with a previous user data validation selection?

Question

Using multiple tiers of drop down lists, I have created a series of user selections. The user selects from the first list on the left. Then depending on the first selection 'Role', the user can select a 'Group'. The final 'Item' selection is based on 'Group'.

However, a user can then go back to select a different 'Role' or 'Group' that doesn't match the 'Item' previously selected.

Is there a mechanism to alert the user immediately that they have entered this erroneous data?

I know that you can 'circle' invalid data, but that seems to require the user to check it themselves, and refresh it each time. If that could be triggered on each change that would be perfect.

Answer 1

Say you set up your data validation lists on Sheet2, and you set up your first data validation on Sheet1!A2. And you wanted to change the validation of Sheet1!B2 based on what was selected in Sheet1!A2. You would place this code in the worksheet module for Sheet1:

Code

Sub worksheet_change(ByVal Target As Range)
If Not Intersect(Target, Range("A2")) Is Nothing Then
    If Target.Value = "one" Then
        With Range("B2").Validation
                 .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Sheet2!B1:B2"
        End With
    End If
    If Target.Value = "two" Then
            With Range("B2").Validation
                 .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Sheet2!B3:B4"
            End With
    End If
    If Target.Value = "three" Then
                With Range("B2").Validation
                     .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Sheet2!B5:B6"
                End With
    End If
End If


End Sub

Then you would set up additional if statements for each additional list that you want to add validation to.

Explanation

Basically this is saying if there's a change on the worksheet, check to see if it's at A2 and it contains a value. This value will only be within your data validation sheet. So if these things are true then:

Check for each possible value and then set the data validation of B2 to the possibilities defined in your data validation list as referenced.

Expand the possibilities to fit your data and add additional tiers for additional lists.