Event triggered by paste

  • Thread starter Thread starter johnhildreth
  • Start date Start date
J

johnhildreth

I found the following on Microsoft's page regarding the OnEntry
Property and Change Event:

The event does not occur if the user click Cut or Paste on the Edit
menu or if another procedure changes the contents of a cell.

So how does one go about triggering an event when data is pasted into a
worksheet?

Thanks,
John
 
John

This should be sort of possible using a Worksheet_Change() event and
checking for Application.CutCopyMode, which indicates if something is on the
clipboard

There are some issues with this, but this simple code demonstrates the
principal

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
If Application.CutCopyMode Then
MsgBox "User is pasting"
Exit Sub
End If
MsgBox "User is not pasting"
End If
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)
 
Actually, the event is triggered when a single cell is pasted. It is
not triggered if the number of cells is more than one.

Still searching...


John
 
John

Sorry, the application.Intersect is restricting it... I always restrict the
cells on which the change event operates. This will work on any cells


Private Sub Worksheet_Change(ByVal Target As Range)
If Application.CutCopyMode Then
MsgBox "User is pasting"
Exit Sub
End If
MsgBox "User is not pasting"
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)
 
Back
Top