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.

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

" wrote:

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.

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

-----Original Message-----
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

" wrote:

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.

.

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


" wrote:

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

-----Original Message-----
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

" wrote:

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.

.

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

" wrote:

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.