Paste Event

[Guest]

I am trying find out if there is a way to trigger macros
when data is pasted into specific areas of a worksheet (or
just when data is pasted in general). The Change Event
doesn't always recognize changes when data is pasted and I
tried the OnKey Event, which works when you use Ctrl+V to
paste, but I could figure out how to recognize paste when
you do edit-paste or right-click-paste, so right now I am
forced to use the Calculate event. But that triggers the
macro every time a any changes are made to a worksheet. I
just want to monitor a specific area.

Thanks.

 

[Guest]

There is an event (BeforeDropOrPaste) that you can use, but I've never used
it myself and it looks quite complicated. Search in VBA help for "Paste
Event"

Mike

 

[Guest]

Is this available in Excel 2000? That is what I have and I
did not see that event.

 

[Guest]

I'm not sure. I using 2003. Sorry I could not be of more help.

 

[Guest]

Well, I found a bit of a work around.

Insert the following code into ThisWorksheet.

Private Sub Workbook_Open()
EnableControl 21, True ' cut
EnableControl 19, True ' copy
EnableControl 22, False ' paste
EnableControl 755, False ' pastespecial
End Sub
Sub EnableControl(Id As Integer, Enabled As Boolean)
Dim CB As CommandBar
Dim C As CommandBarControl

On Error Resume Next
For Each CB In Application.CommandBars
Set C = CB.FindControl(Id:=Id, recursive:=True)
If Not C Is Nothing Then C.Enabled = Enabled
Next

End Sub

This will disable the Paste and PasteSpecial functions in both the Edit and
Right click menus. Then the user is forced to use Ctrl-V to paste. You can
then use the OnKey event that you already have.


Mike