Clear dependent dropdown lists

[Mylarbi]

Hi, I have a table with parent dropdown lists in columns 18, 30 and 34. Each one has a dependent dropdown lists in the second column to the right of each (that is columns 20, 32 and 36).
I want each dependent list cell to clear whenever its parent list is change. I have therefore created this code below in the sheet code of my macro-enabled files but it does seem to work. Kindly help.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 18 Then
 If Target.Validation.Type = 3 Then
 Application.EnableEvents = False
 Target.Offset(0, 2).ClearContents
 End If
 End If
 On Error Resume Next
If Target.Column = 30 Then
 If Target.Validation.Type = 3 Then
 Application.EnableEvents = False
 Target.Offset(0, 2).ClearContents
 End If
End If
On Error Resume Next
If Target.Column = 34 Then
 If Target.Validation.Type = 3 Then
 Application.EnableEvents = False
 Target.Offset(0, 2).ClearContents
 End If
 End If
 End Sub

 

[juddaaaa]

Hi Mylarbi. Welcome to the forum

First things first. It's not a good idea to use On Error Resume Next. If you're getting errors, it's better to try and figure out what's causing them and fix it.

However, if you are going to use it you only need to enter it once at the beginning of your code. To revert back to normal you can use On Error Goto 0.

The same thing can be said for Application.EnableEvents = False. Just once at the beginning of your code is all it takes. Just remember to re-enable it at the end of your code with
Application.EnableEvents = True

Here's what your code should look like

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    
    If Target.Column = 18 Then
        If Target.Validation.Type = 3 Then
            Target.Offset(0, 2).ClearContents
        End If
    End If
    
    If Target.Column = 30 Then
        If Target.Validation.Type = 3 Then
            Target.Offset(0, 2).ClearContents
        End If
    End If
    
    If Target.Column = 34 Then
        If Target.Validation.Type = 3 Then
            Target.Offset(0, 2).ClearContents
        End If
    End If
    
    Application.EnableEvents = True
End Sub

 

[Mylarbi]

@juddaaaa Thanks for your help. I have used your suggested code. The function is a bit better than before but the main target to clear the dependent dropdown list is still not working. Please see

. Thanks

 

[Mylarbi]

@juddaaaa I just closed all applications and restarted my system. Now everything is working as expected with your code.

Thank you very much.

 

[juddaaaa]

You're welcome