Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change and Copy
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change and Copy
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change and Copy
Can't duplicate it, Ron. I either get "Processed" in a1 and a12 or nothings
if I change out of target. sorry. "Ron Rosenfeld" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change and Copy
On Mon, 25 Feb 2008 15:04:01 -0800, JLGWhiz
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change | Excel Programming | |||
Worksheet_Change Event - Macro kills copy and paste | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |