Excel and changing the value between two drop-down lists

Question

I have two drop-down lists: one depends on the other - this means that if I select a specific value from a list in A1, a specific list will appear in A2. It works great. However, when I change the value in A1, A2 remains in the cell until I click on the list - then my value in A2 will change depending on my choice.

For example, if list 1 contains ['Yes', 'No'], and the list contains Yes: [1,2,3] No: [4,5,6]. First, I select “Yes” for A1, and then I select 2 for A2. Then, if I select “No” for A1, “2” will remain in A2 until I actually click on A2 to select a new value (4,5,6). Is there a way to “clear” A2 as soon as I change the selection to A1?

Thanks!

Answer 1

Put this code on the code page for Worksheet :

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

Answer 2

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 3

You can paste the code to update list A2 in the workheet_change procedure for your worksheet. Each time a cell value changes on this sheet, your update code will work.