Multiple Cell Change Events

[sloesch]

Hello,

I have a sheet where users enter inputs, and I want the sheet to clear all inputs when they change the account name. The code for this is below, and was successfully implemented:

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("C4")) Is Nothing Then
Range("C8").Value = "No"
Range("A12").Value = "No"
Range("C12:K12").ClearContents
Range("C13").ClearContents
Range("C15").ClearContents
Range("C17:C18").ClearContents
Range("C20:C21").ClearContents
Range("D20:I20").ClearContents
Range("D54").ClearContents
Range("A20").Value = "No"
Range("C19").Value = 2
Range("A53").Value = "Select Ticket Type"
Range("B57:B72").Value = "No"
Range("C57:J72").Value = 0
End If
End Sub

However, I want a similar thing (clear different cells) to happen when the user makes changes to cell A53. I've tried copying/pasting the above macro into the same sub (after modifying) and the effect did not take. I also tried creating a separate subroutine with A53 substituted for C4 in line 3 with no effect. In both scenarios, the original code still works.

In essence, how can I make sure that both cell-change events trigger the desired routines?

Thanks,

 

[HalfAce]

Here's something that might help.
I shortened up the code a bit as you don't need separate lines for different ranges getting the same treatment. (ie, the ClearContents)
Also, you need to disable Events as range A53 is getting changed in both cases so it would just keep executing the code over & over & over forever...

Try this and see if it works the way you want.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Quit
Application.EnableEvents = False
If Not Intersect(Target, Range("C4")) Is Nothing Then
  Range("A12,C8, A20, B57:B72").Value = "No"
  Range("C12:K12,C13,C15,C17:C18,C20:C21,D54").ClearContents
  Range("C19").Value = 2
  Range("A53").Value = "Select Ticket Type"
  Range("C57:J72").Value = 0
End If

If Not Intersect(Target, Range("A53")) Is Nothing Then
  Range("A12,C8, A20, B57:B72").Value = 'Whatever you want to go here
  Range("C12:K12,C13,C15,C17:C18,C20:C21,D54").' Whatever you want to do here
  Range("C19").Value = 'Whatever you want to go here
  Range("A53").Value = 'Whatever you want to go here
  Range("C57:J72").Value = 'Whatever you want to go here
End If

Quit:
Application.EnableEvents = True

End Sub

Hope it helps.

 

[Rmarvin]

There is a circlular reference if you use A53 as a change event with the sub changing A53. This will cause a loop and crash excel.
Try changing either the reference to A52, or the destination of "Select Ticket Type"
This will put "Select Ticket Type" in A52, and run if a change is made to A53:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Ranges As Range
    Set Ranges = Union(Range("C4"), Range("A53"))
    If Not Application.Intersect(Ranges, Range(Target.Address)) Is Nothing Then
        Range("C8").Value = "No"
        Range("A12").Value = "No"
        Range("C12:K12").ClearContents
        Range("C13").ClearContents
        Range("C15").ClearContents
        Range("C17:C18").ClearContents
        Range("C20:C21").ClearContents
        Range("D20:I20").ClearContents
        Range("D54").ClearContents
        Range("A20").Value = "No"
        Range("C19").Value = 2
        Range("A52").Value = "Select Ticket Type"
        Range("B57:B72").Value = "No"
        Range("C57:J72").Value = 0
End If
End Sub

 

[sloesch]

Thank you for responses. Halface - I cleaned up the code and removed A53 from the first section, so it looks like such. However, changing A53 does not trigger the desired changes. Additionally, even though I removed the reference of A53 from the first section, when I change C4, A53 still reverts to 'Select Ticket Type.'

Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo Quit
Application.EnableEvents = False
If Not Intersect(Target, Range("C4")) Is Nothing Then
Range("A12,C8,A20,B57:B72").Value = "No"
Range("C12:K12,C13,C15,C17:C18,C20:C21,D54").ClearContents
Range("C19").Value = 2
Range("C57:J72").Value = 0
End If


If Not Intersect(Target, Range("A53")) Is Nothing Then
Range("B57:B72").Value = "No"
Range("C12:E12,C13,C15,C17:C18,D54").ClearContents
Range("C19").Value = 2
Range("C57:J72").Value = 0
End If


Quit:
Application.EnableEvents = True


End Sub

RMarvin - I adjusted the macro to see if your method worked, and unfortunately, changing A53 did not trigger changes either.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ranges As Range
Set Ranges = Union(Range("C4"), Range("A53"))


If Not Intersect(Ranges, Range(Target.Address)) Is Nothing Then
Range("A12,C8,A20,B57:B72").Value = "No"
Range("C12:K12,C13,C15,C17:C18,C20:C21,D54").ClearContents
Range("C19").Value = 2
Range("C57:J72").Value = 0
End If



I was also hoping that someone could explain to me that even after I've removed the reference to changing A53 from the original Worksheet_Change routine that modifying C4 continues to do so?

Thank you in advance.

 

[HalfAce]

OK, when I use the code you last posted, (the top one) the routine does fire off when I change A53, so that's doing what it's being told to do.

When I make a change in C4, I do not get any change in A53.

But most of all, using my original posted code eliminates the problem of A53 causing a re-occurring loop by turning the events off at the start and then back on at the end, so in reality you should be able to keep your original set up while using that code.

Does that not do what you were originally wanting to do?

EDIT:
Did by chance your code get interrupted at some point (bugged out or manually) while you were testing it?
If that's the case then you may still have events disabled, which would prevent the code from firing when you changed A53 in further testing... just a thought.

 

[sloesch]

Essentially the file has two sections of code - one that triggers after a button is pressed which creates a new worksheet in the same document but does not modify any existing sheets, and the one I'm working on now dealing with worksheet changes.

What seems to be happening here is that the original coding (in my first post) is triggering despite what is coded in the Worksheet Change section. To test this, I actually deleted all code from that section and the original event still triggers.

Any idea to why this would happen?

 

[HalfAce]

I'm a bit confused.
What does all your worksheet change code look like now?

 

[sloesch]

I'm a bit confused.
What does all your worksheet change code look like now?

Right now it looks like below. However no matter what changes I make, it seems that the original coding is activated.

Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo Quit
Application.EnableEvents = False
If Not Intersect(Target, Range("C4")) Is Nothing Then
Range("A12,C8,A20,B57:B72").Value = "No"
Range("C12:K12,C13,C15,C17:C18,C20:C21,D54").ClearContents
Range("C19").Value = 2
Range("C57:J72").Value = 0
End If


If Not Intersect(Target, Range("A53")) Is Nothing Then
Range("B57:B72").Value = "No"
Range("C12:E12,C13,C15,C17:C18,D54").ClearContents
Range("C19").Value = 2
Range("C57:J72").Value = 0
End If


Quit:
Application.EnableEvents = True


End Sub

 

[HalfAce]

If that's literally all that's there, between Private Sub and End Sub, then I don't see how anything else can be getting called, done or invoked.

Unless you have some (the original?) code somewhere else, like in the ThisWorkbook module in a Workbook_SheetChange event. But I get the feeling that's not going to be the case.