Add Event Code to Clear the Customer Cell

whytewolves

New Member
Joined
Sep 16, 2015
Messages
21
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
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:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Each Bill has a indirect reference
Creditor and the other Headers have indirect data validation as well

I am still struggling to understand this. Do you have formulas in columns B:F? any of them, all of them. Can you post one to show what it does? Do you have any VBA code use currently? if you can give some specific answers to these, we might be able to resolve your issues.
 
Upvote 0
No formulas, just drop down lists and multiple data validation
Column F will bring in amount from another worksheet with updated amounts based on cell B:E drop down lists
Eventually, there will be a macro to highlight the rows when I click in a cell and further columns will have "SUM" formulas
 
Upvote 0
This is a modified version of Snakehips' code. If it does not work for you, I don't know what to tell you.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Column = 2 Then Exit Sub
On Error Resume Next
    If Target.Value = "" Then
        If Err.Number = 0 Then
            Application.EnableEvents = False
            Target.Offset(, 1).Resize(1, 3).ClearContents
            Application.EnableEvents = True
        End If
    End If
On Error GoTo 0
End Sub
It is event code for the worksheet code module. Right click the sheet name tab, then click 'View Code' in the pop up menu to access the code module. Copy and paste this code into the large pane. If you already have a worksheet change code in that module you will need to either delete the other one or merge the two into one. Excel cannot support more than one change event code per sheet.
 
Last edited:
Upvote 0
This worked great.
Thanks.
Do I need to insert into each worksheet for each month?
 
Upvote 0
This worked great.
Thanks.
Do I need to insert into each worksheet for each month?

That's one way of doing it.

Or you could make it a Workbook_SheetsChange macro if you only have that type sheet in the workbook, or if you can describe which sheet are exceptions , I could whip up a code that would work in the ThisWorkbook module to cover all the month sheets.

For the ThisWorkbook code module, if all sheets are month sheets.

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Target.Column = 2 Then Exit Sub
On Error Resume Next
    If Target.Value = "" Then
        If Err.Number = 0 Then
            Application.EnableEvents = False
            Target.Offset(, 1).Resize(1, 3).ClearContents
            Application.EnableEvents = True
        End If
    End If
On Error GoTo 0
End Sub
 
Last edited:
Upvote 0
So, I would name my sheets Jan - Dec and this macro will work only on those sheets in the workbook?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top