CODEWITHSUNDEEP
excelvba
Derrick
Derrick

Reputation: 11

Clearing a cell based on a dependent drop down box in VBA

I have a drop down box in I8, and I have a dependent drop down box in I18 that is directing to I8. If perhaps apple1 is chosen in I8, I will get fruits 1-10 listed in I18 when I click on that box. Say I chose Fruit 9 for the box in I18. Well, the problem is that say I decide to change my I8 answer to apple2, Fruits 1-5 will appear in I18 but 6-10 will not. Now in I18, Fruit 9 is still chosen, and I will have to click to change it.

I want to make it to where if I have chosen Fruits 6-10 while apple1 is chosen, and I decide to change it to apple2, my I18 will go blank.

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$I$8" Then
    If Target.Address = "6" Or "7" Or "8" Or "9" Or "10" Then
    If Target.Validation.Type = 3 Then
    Application.EnableEvents = False
    Target.Offset(10, 0).ClearContents
    End If
    End If
End If

exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub

Upvotes: 1

Views: 241

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27259

A few issues. See notes in refactored code.

Private Sub Worksheet_Change(ByVal Target As Range)

'On Error Resume Next ->> delete this, it will not allow you to see real errors in your code

If Target.Address = "$I$8" Then

    'you ask it define Target.Address = 6, etc. Wrong property. You need Target.Value (
    'think about it, you just checked for Address = $I$8, so how can it also be 6 - 10
    'also if you do want to use IF, syntax is Target.Value = 6 or Target.Value = 7 ... not 6 or 7 or ...

    Select Case Target.value 'used this as it more efficient

          Case 6 to 10

             If Target.Validation.Type = 3 Then
                 Application.EnableEvents = False
                 Target.Offset(10, 0).ClearContents
             End If

    End Select

End If

exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub

Upvotes: 2

Related Questions