Solved: Worksheet_Change and .PasteSpecial

[TheAntiGates]

Would Worksheet_Change be triggered by
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationAdd
? I'm seeming to see that it's not in a project I'm on now. application.enableEvents is true.

[Bob Phillips]

It looks as if it is to me.

[malik641]

I don't get what you mean by "seeming to see", but it should trigger the _Change event to the workbook that's being effected.

Post more of the code. Let's see what's really going on.

[TheAntiGates]

Thank you for confirming that it appears amiss. There are hundreds of code lines, but here are key elements:

The change code isPrivate Sub Worksheet_Change(ByVal Target As Range)
If (Target.row = 49) And (Target.Column = 8) Then Stop
End Sub
.ClearContents empties the target cell; .pastespecial puts a value there (empty or blank becomes 90); the Stop does not happen. However, if I now hit the delete key on the cell of interest, it fires. .EnableEvents is never explicitly set, though .DisplayAlerts and .ScreenUpdating are toggled.

(The code is Sheet19 code)

[TheAntiGates]

This had a logical explanation. Change event fires once for a block paste, not once per cell; so what arrives as Target.column is the first column in the range. Too bad. I really hoped to monitor one of those cells this way, such as you could set a breakpoint on a variable in Turbo Debugger or CodeView. I guess I could save the prior value in a global and test it manually in the change area.

Now that I think of it, maybe the valuable lesson here is to test .Intersect instead of .row and .column above. I think I'll adopt that policy hence. Maybe define my range to monitor for changes as Foo (even if one cell only) and
If Not Application.Intersect(Target, Range("Foo")) Is Nothing Then stop

[Bob Phillips]

Check each cell



Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

For Each cell In Target

If (cell.Row = 49) And (cell.Column = 8) Then Stop
Next cell
End Sub

[TheAntiGates]

In lieu of Intersect? I'll try to run speed tests. It's no surprise when Microsoft-written functions are slower than home grown - but I sure wouldn't have guessed that here.

[Bob Phillips]

Nothing to do with Intersect, or Row/Column, it is all about checking each cell in target.