Worksheet_Change and Copy

[Ron Rosenfeld]

I've tried to simplify the code as best I can to illustrate the problem I don't
understand.

Apparently, if I copy data from a location on the worksheet, and then paste
this data into "Target", the worksheet_change event gets triggered. However,
if I try to write to Target.value, or even another cell, an error is generated.

1004 Application-defined or object-defined error


Here is the code:

=========================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rRng As Range
Dim Str As String
Set rRng = Range("A1")
If Not Intersect(Target, rRng) Is Nothing Then
Application.EnableEvents = False
On Error GoTo Handler
Target.Value2 = "Processed"
Range("a12").Value = "Processed"
Application.EnableEvents = True
End If
Exit Sub
Handler:
Debug.Print Err.Number, Err.Description
Resume Next
End Sub
========================

Can someone explain to me what is going on? And if there is a way around this?

I did try setting CutCopyMode to False before doing the cell write, but that
had no affect. It neither got rid of the error, nor change the CutCopyMode!

Thanks
--ron

 

[dan dungan]

Apparently, if I copy data from a location on the worksheet, and then >paste this data into "Target", the worksheet_change event get triggered. >However, if I try to write to Target.value, or even another cell, an error is >generated.

I copied your code and pasted it in Sheet1 module.

I see no errors when I change the value in a1. I do not see any values
being copied.

 

[JLGWhiz]

Can't duplicate it, Ron. I either get "Processed" in a1 and a12 or nothings
if I change out of target. sorry.

 

[Ron Rosenfeld]

Can't duplicate it, Ron. I either get "Processed" in a1 and a12 or nothings
if I change out of target. sorry.

Thanks to you and Dan for trying it out. After seeing that you could not
reproduce it, I cleaned out all the extraneous "stuff" in that workbook and
discovered that I, too, am now unable to reproduce it. Must have been do to
something that wasn't necessary.

Thanks again.
--ron