VBA code to reset drop down list

[Phx007]

Hello. I am in need of some help here.

I have 2 drop down lists on my spreadsheet.

what I need is so when I select an entry in drop down list #1 for the other drop down list #2 to clear. Vice Versa I also need the same code to clear drop down list #1 when drop down list #2 options is selected. Thank you!

 

[Peter_SSs]

Welcome to the MrExcel board!

when I select an entry in drop down list #1 for the other drop down list #2 to clear.

Do you really mean for the other list to clear or do you mean to clear the other cell that has the other drop-down list as its Data Validation list.

 

[Phx007]

Hello , sorry I might have typed my question in a hurry. So what I mean is that I want list #1 to clear any validation that is currently selected when any validation is selected from list #2. So I basically want the two lists to cancel each other out when one of the other is selected.

If any drop down validation is selected from list #1 then list #2 clears AND If any drop down validation is selected from list #2, list #1 clears. Hopefully that made sense thank you so much!

 

[Phx007]

I have tried to use this code but it freezes my excel. It works perfectly if I just have one target address but then only one drop down list resets and I am left with the second one that I have to manually clear


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$G$4" Then

Range("G6").Value = "Please Select..."

End If

If Target.Address = "$G$6" Then

Range("G4").Value = "Please Select..."

End If

End Sub

 

[Peter_SSs]

but it freezes my excel.

That is because when you change one of the cells, your code changes the other one which triggers this worksheet change code which will clear the first cell again which triggers this worksheet change code which will clear the second cell again which triggers this worksheet change code ...

You just need to add a few lines to stop that re-triggering.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Address = "$G$4" Then
    Application.EnableEvents = False
    Range("G6").Value = "Please Select..."
    Application.EnableEvents = True
  End If
  
  If Target.Address = "$G$6" Then
    Application.EnableEvents = False
    Range("G4").Value = "Please Select..."
    Application.EnableEvents = True
  End If

End Sub

 

[DanteAmor]

Just another way:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("G4, G6")) Is Nothing Then
    Application.EnableEvents = False
    Range(IIf(Target.Address(0, 0) = "G4", "G6", "G4")).Value = "Please Select..."
    Application.EnableEvents = True
  End If
End Sub

 

[Phx007]

Thank you all! both worked like a charm. I really appreciate it!

 

[DanteAmor]

Glad we could help & thanks for the feedback