1

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.

2
  • How about defining the options of the second dropdown by the selection of the first? Every time the first is activated, clear the rest, and reset the options. Sep 5, 2014 at 11:51
  • How would I perform that?
    – DaveRGP
    Sep 9, 2014 at 7:53

1 Answer 1

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.

1
  • Sorry, just spotted this. Hecticness means I won't be able to test this for a few days, when I do I'll obviously see if this works for me. Unsure how I'll manage it as yet though. I'll assume the answer is correct for now, and mark it as such.
    – DaveRGP
    Sep 12, 2014 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.