Vba Multiple Target. Address (Or) for the Same Action
In a Worksheet I Have Two Drop-Down Lists (Cells C7 and C68) Which Each Have a Dependent Drop-Down in the Cell Below. I Have a Code (Below) Which Will Clear...
In a worksheet I have two drop-down lists (cells C7 and C68) which each have a dependent drop-down in the cell below. I have a code (below) which will clear the cell of the dependent drop-down if I change the selection in the above list (so that the lists do not mis-match), however I can only get this to work for the one drop-down in the sheet. How can I amend this to that it works if I alter either of the cells with the "Parent" list?
.
Existing code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$7" Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = True
Target.Offset(1, 0).Value = ""
End If
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
2 Answers
I suggest using only 1 dropdown list (which user actually selects) and then 2nd "linked cell" is using vlookup from some kind of data transformation list.
All fixed - for anyone who also has this problem, the correct code was:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$7" Or Target.Address = "$C$68" Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(1, 0).Value = ""
End If
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub
James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.
