Reset dependent drop down lists in excel

0
votes

Good day, I have an excel question on how to reset dependent drop downs. I have used VBA to reset one drop down list, using the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$14" Then
    Range("I14").Value = "Please select..."
End If
End Sub

How can I change that so that it applies to a range of rows? Like if a drop down on a row anywhere between H14:H50 is changed, then the depending drop down on the same row in the range from I14:50 gets reset and shows "Please select..."

There must be a better way then this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$14" Then
    Range("I14").Value = "Please select..."    
End If
If Target.Address = "$H$15" Then
    Range("I15").Value = "Please select..."
End If

If Target.Address = "$H$16" Then
    Range("I16").Value = "Please select..."        
End If
End Sub
2
excelvbadrop-down-menu

2 Answers

0
votes

That would be the Intersect method. That can check if Target is within your monitored Range. Intersect returns the shared area between the two ranges, so you would just need to check if that range exists at all to find out if Target is within H14:H50.

You would check it like this:

If Not Intersect(Target,Range("H14:H50")) Is Nothing then
    Cells(Target.Row, 9) = "Please Select..." 
End If
0
votes

Toddlesons answer is really what you want as that's the better way to do it, but for the way your wanting you first need to check the column then the row.

So:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 8 Then 'Column H
    If Target.Row >= 14 And Target.Row <= 50 Then
        Cells(Target.Row, 9) = "Please Select..."
    End If
End If

End Sub