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.
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.
I just found a cool semi-advanced VBA page - dictionary, queue, etc. http://analystcave.com/excel-vba-dic...ta-structures/
It looks as if it is to me.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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.
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
Thank you for confirming that it appears amiss. There are hundreds of code lines, but here are key elements:
The change code is[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.row = 49) And (Target.Column = 8) Then Stop
End Sub
[/vba].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)
I just found a cool semi-advanced VBA page - dictionary, queue, etc. http://analystcave.com/excel-vba-dic...ta-structures/
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
I just found a cool semi-advanced VBA page - dictionary, queue, etc. http://analystcave.com/excel-vba-dic...ta-structures/
Check each cell
[vba]
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
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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.
I just found a cool semi-advanced VBA page - dictionary, queue, etc. http://analystcave.com/excel-vba-dic...ta-structures/
Nothing to do with Intersect, or Row/Column, it is all about checking each cell in target.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber