Add Event Code to Clear the Customer Cell

whytewolves

New Member
Joined
Sep 16, 2015
Messages
12
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.

BillsCreditorTypeCompany
Court
Credit
Capitol One
EntertainmentShoppingOnline
PastPastUtilitiesJonElectric

<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.
 
Last edited:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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?
 
Last edited:

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Try this for the clear offset line.

Howard

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

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
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.

Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

New Member
Joined
Sep 16, 2015
Messages
12

ADVERTISEMENT

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

New Member
Joined
Sep 16, 2015
Messages
12
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

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,387
Messages
5,595,879
Members
414,029
Latest member
mrwilker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top