Add Event Code to Clear the Customer Cell

[whytewolves]

I have a worksheet that has 3 dependent cells following the first. I need to clear all cells when first data validation box is changed
This code i found only clears the cell following the target cell, and not the other two cells following.

Bills	Creditor	Type	Company
Court
Credit	Capitol One
Entertainment	Shopping	Online
Past	PastUtilities	Jon	Electric

<tbody>
</tbody>

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

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub


Id also like to be able to add say "ATM" to Credit without having to insert credit into column A again each time.
I have Data Validation and my lists created, just dont want to have to pick same Bill for a new creditor each time.

 

[JLGWhiz]

What do you mean by dependent cell?. Normally if you clear the precedent cell the dependent cells will also clear. How are your cells dependent and which cells are they dependent upon and how?

 

[L. Howard]

Try this for the clear offset line.

Howard

Target.Offset(0, 1).Resize(1, 3).ClearContents

 

[Snakehips]

whytewolves,

Your original code will clear the contents of the cell to right of target cell regardless of whether target has data validation of any type or none at all.
I'm thinking the below code will do what you want.

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Target.Column = 2 Then Exit Sub
On Error Resume Next
    If Target.Validation.Type = 3 Then
        If Err.Number = 0 Then
            Application.EnableEvents = False
            Target.Resize(1, 3).Offset(0, 1).ClearContents
            Application.EnableEvents = True
        End If
    End If
On Error GoTo 0
End Sub

Hope that helps.

 

[JLGWhiz]

The OP stated that there were dependent cells. If there are formulas in the cells, then clearing them will destroy the formulas.

 

[L. Howard]

The OP stated that there were dependent cells. If there are formulas in the cells, then clearing them will destroy the formulas.

I took it they were dependent drop downs, where upon reselecting in the first, the previous selections in the other dependent drop downs are not relevant to the new first drop down selection and clearing them prevents possible confusion.

Howard

 

[whytewolves]

How can I post the sheet so you can see how the cells are dependent?
The top line, Bills and etc are simply Headers
Each Bill has a indirect reference
Creditor and the other Headers have indirect data validation as well
When I change the Bill cell reference, I would like all other corresponding cells to go blank so that I can then pick which Bill type I need in the cell

 

[whytewolves]

Bills	Creditor	Type	Company	Owe
Court				4163
Credit	Capital One			200.89
Entertainment	Shopping	Online
Past	PastUtilities	Jon	Electric	787.50

<tbody>
</tbody>

 

[L. Howard]

How can I post the sheet so you can see how the cells are dependent?

You can use one of the link utilities like Drop Box to provide a link to your example workbook. You cannot attach a sheet/workbook here.

With the link, re-state what you want to happen and where, refer to specific cells, rows, columns in your description.

Howard

 

[JLGWhiz]

Here is a link to a video of how to use the HTML maker to display your sheet.

https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be