Excel clear contents of drop down based on another cell being cleared

Question

I have a cell on Sheet1 that contains a drop-down list, let's say N3. The items in the drop down change, depending on the value in J3. At start, both cells are blank. Enter data in J3, and the drop down populates in N3. If I clear the contents of J3, the drop down in N3 is now empty, but the last selected value (if one was selected), still appears as a 'ghost' entry. It's a ghost entry to ME because it is old data, but I do understand the software is doing as designed. If J3 is cleared of contents, how do I get N3 to be cleared of that last selection? I am not VBA trained, but dangerous enough to handle it if that's what's needed to accomplish this. thanks!

If below answer was helpful please accept it by clicking the checkmark next to it. Read more here — Santosh

Santosh Santosh · Accepted Answer · 2013-10-11T19:47:35

You may consider to use the worksheet_change event.Put the below code in sheet1 code module.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo err_rout

    Application.EnableEvents = False

    If Not Intersect(Range("J3"), Target) Is Nothing And Target.Value = vbNullString Then
        Range("N3").Value = vbNullString
    End If

err_rout:
    Application.EnableEvents = True
End Sub

Excel clear contents of drop down based on another cell being cleared

1 Answers