Why is same ChangeEvent behaving differently under a different scenario

[julhs]

At moment I can’t get to bottom of it.
WHY;
if I manually insert blank cells into a “NamedRange” that my ChangeEvent calls all the associated call events, JUST the ONCE & Sub will End at command.
But if I manually Copy/Paste/Insert the EXACT same number of cells into EXACT same cells of the “NamedRange”, the same ChangeEvent is being fired TWICE before it will End Sub.

 

[Engberg]

Post your code? Also, what do you mean by "manually insert" and "manually Copy/Paste/Insert"?

 

[6StringJazzer]

Please show the code for the entire sub Worksheet_Change.

When you say "insert", are you actually inserting rows or columns in the middle of existing data, rather than overwriting existing cells? Can you give a concrete description of what cells are in "NamedRange", what it looks like to start with, and what it looks like after you update it?

 

[julhs]

Thank you both for response.
Will take me a while to clean up/condense my code to something you can make use of.
But by “Manually” I mean, LeftClick select some cells and then select new location in the NamedRange, then RightClick and either select “Insert” or “Insert Copied cells”
Until I have clean/condensed my code for you to see, it’s not worth me saying any more.

 

[julhs]

Not sure if this is enough for you to dissect the issue?

Private Sub Worksheet_Change(ByVal Target As Range)
' Some un-associated ChangeEvent code here.
If Not Intersect(Target, Range("ChemicalSuppliersList_Condensed")) Is Nothing Then
LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Call DeletCellsAndFillDown
Call ReFormatRange
End If
' More un-associated Change code here
End Sub

If I only "Insert" blank cells into ("ChemicalSuppliersList_Condensed") then the ChangeEvent will end immediately on End Sub
But if I use "Copy/Paste/Insert" into ("ChemicalSuppliersList_Condensed") then the ChangeEvent will NOT end immediately on End Sub,
instead it goes back to "If Not Intersect(Target, Range("ChemicalSuppliersList_Condensed")) Is Nothing Then"
and the 2 call events are called again and only then does the Sub End

 

[Engberg]

Seems that I can replicate the behavior as well, so it's probably not your code. Not sure what is causing it.

 

[julhs]

In some ways what you said gives me a degree of encouragement that it’s not simply of my code.
I’m currently experimenting with ways out of this issue, but would still value any further thoughts you may have.

 

[Engberg]

Did some more testing, it seems to have to do with what Right click > Insert copied cells does. If you record a macro and insert copied cells, you get something like this.
I can only come to the conclusion that Range.Insert does two changes to the sheet in sequence, probably first moving existing values (first Worksheet_Change triggers), and then pasting the new values (Worksheet_Change triggers again).

Sub record_insertCopiedCells()
    Range("K7:R20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A5").Select
    Selection.Insert Shift:=xlDown
End Sub

 

[julhs]

Again, what you said gives me a degree of encouragement.
That was sort of my thinking, HOWEVER I couldn’t come up with way counter act it.

 

[6StringJazzer]

Not sure if this is enough for you to dissect the issue?

Many issues in Worksheet_Change are caused by unintended recursive calls when that Sub makes its own changes.

I did not dig down into the details but before you do anything else I would suggest that you add this at the beginning of the Sub

Application.EnableEvents = False

and add this at the end

Application.EnableEvents = True

If you still have the problem then I'll take your code and see if I can reproduce your problem.