Clear remaining cells when value is added

Question

In Excel I have a Table with about 8 products(8 rows). (the table is a drop down list and can be sorted on alphabet) The table auto expands when a new product is added at the bottom of the list.

The table is set up like explained below:

Column B = Product Number

Column C = Product Name

Column D = Certain Value

Column E = Certain Value

Column D and E are Usually empty, I want only 1 cell in the whole range(D1:E8) to contain any value. If a new value is added, all the other Cell's in this range need to be cleared.

Is This possible by using a VBA Macro? (If so... Then how?)

For Example,

D3= "x". When a string "x" is entered in cell E6, all other cells (including D3 need to become empty). I'd like to do this by starting a VBA macro, so I could add some additional actions that need to happen after one of the products is selected with an "x"in Column D or E. I know this can be done by a userform as well, but would prefer to do it this way.

SierraOscar SierraOscar · Accepted Answer · 2015-10-26T14:57:14

In the Worksheet module of the sheet you want this to affect use:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("D1:E8")) Is Nothing Then
    If Target.Cells.Count > 1 Then
        MsgBox "Please edit one cell at a time!"
    Else
        Application.EnableEvents = False

        newVal = Target.Value
        Range("D1:E8").ClearContents
        Target.Value = newVal

        Application.EnableEvents = True
    End If
End If

End Sub

Clear remaining cells when value is added

1 Answers