Excel and changing value between two drop down lists

Question

I have two drop down lists -- one is dependent on the other -- meaning if I select a particular value from list in A1, a specific list appears in A2. This works just fine. However, when I change the value in A1, A2 stays in the cell until I click on the list -- then my value in A2 will change based on my selection.

For example, if list 1 is ['Yes','No'] and list to is Yes: [1,2,3] No: [4,5,6]. First I select 'Yes' for A1 and then select 2 for A2. Then, if I select 'No' for A1, "2" stays in A2 until I actually click on A2 to select a new value (4,5,6). Is there any way to "clear" A2 once I've changed the A1 selection?

Thanks!

Answer 1

put this in the VBA code for your worksheet:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = Range("A1").Address Then
        Dim dependentCell As Range
        Set dependentCell = Target.Offset(1, 0)     'Cell A2
        If dependentCell.Validation.Value = False Then dependentCell.Clear
    End If        
End Sub

Answer 2

Put this code in the code page for your Worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("A1").Address Then
        Range("A2").ClearContents
    End If
End Sub