Validating a List Selection

**wilderfan** · 01-25-2013, 02:02 PM

Is it possible to validate a list selection?

I have 2 very simple lists which include Yes and No.

The headings of the 2 columns are: Resident in California? and Resident in the USA?

If the user selects Yes to the 1st question, then by default the user must select Yes to the second question.

However, users might answer the 2nd question first. In that case, the user selects No to being resident in the USA, then by default the user must select No to being resident in California.

At this point, I'm thinking of just introducing a column for error comments and using the traditional IF statements to validate the 2 columns.

QUESTION: Is there a better solution ?

**FDibbins** · 01-25-2013, 02:17 PM

see oeldere's links in this post...

http://www.excelforum.com/excel-gene...alidation.html

**Sean Thomas** · 01-25-2013, 02:32 PM

Hi
first code will populate the USA question based on the California question
2nd code will check if 1st question has been answered first

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("A2:A15"), Target) Is Nothing Then
        If Target.Value = "Yes" Then ' california
         Target.Offset(0, 1).Value = "Yes" 'USA
         Else
         Target.Offset(0, 1).Value = "No" 'USA
        End If
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
       
    If Not Application.Intersect(Range("B2:B15"), Target) Is Nothing Then
        If Target.Offset(0, -1) = "" Then
        MsgBox "Answer California question first"
        Target.Value = ""
        Target.Offset(0, -1).Activate
        End If
    End If
    If Not Application.Intersect(Range("B2:B15"), Target) Is Nothing Then
        If Target.Offset(0, -1) = "Yes" Then
        Target.Value = "Yes"
        End If
    End If
End Sub