PDA

View Full Version : Solved: worksheet_change event does not trigger on copy/paste



tools
06-01-2008, 10:36 PM
Hi all,

when i paste some values (rows) from one excel sheet to another the worksheet_change event is not triggered properly

Here is the code that i have written
Is there any other event which could trigger this .?




Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.Unprotect

On Error Resume Next
Dim rFound As Range
Dim rowlast
If Not Intersect(Target, Range("D4,U4,C10:C100,E10:E100,G10:G100")) Is Nothing Then
If Target.Column = 4 Then
Set rFound = Sheets("TEMP").Cells.Find(What:=Target.Value, After:=Sheets("TEMP").Range("D1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
Target.Offset(0, 17).Value = rFound.Offset(0, 17).Value
End If

End If


Sheet1.Protect


If Sheet1.Protection.AllowInsertingRows = False Then
Sheet1.Protect AllowInsertingRows:=True
End If

End Sub

tools
06-01-2008, 10:54 PM
The event gets triggered and it shows the target column number as 1

Is there a way where i can check the columns in which data has been entered

Bob Phillips
06-02-2008, 12:32 AM
Do you mean if more than one cell changed at a time?

Simon Lloyd
06-02-2008, 12:38 AM
Perhaps at the following lines in your code (which by the way just changes a single value!),

.......Target.Offset(0, 17).Value = rFound.Offset(0, 17).Value
MsgBox Target.Offset(0, 17).Address
End If

Simon Lloyd
06-02-2008, 12:41 AM
I don't think we have been supplied the whole code Bob there is a Dim which doesnt reference anything!

tools
06-02-2008, 02:58 AM
Yes if more than one cell has been changed then this code does not work.

Bob Phillips
06-02-2008, 04:02 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rFound As Range
Dim rowlast
Dim cell As Range

Sheet1.Unprotect

On Error Resume Next
If Not Intersect(Target, Range("D4,U4,C10:C100,E10:E100,G10:G100")) Is Nothing Then

For Each cell In Target

If cell.Column = 4 Then

Set rFound = Sheets("TEMP").Cells.Find(What:=cell.Value, _
After:=Sheets("TEMP").Range("D1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
cell.Offset(0, 17).Value = rFound.Offset(0, 17).Value
End If
Next cell
End If

Sheet1.Protect

If Sheet1.Protection.AllowInsertingRows = False Then
Sheet1.Protect AllowInsertingRows:=True
End If

End Sub

tools
06-02-2008, 05:44 AM
Thanks xld :)