How do you trap the paste event in a worksheet?

[Dreiding]

I need to make sure that anyone pasting data into a worksheet uses the paste
special-value method. Is there a way to trap the "paste" execute and
automatically change it to a paste special - values?

Thanks,
- Pat

 

[Simon Lloyd]

This isn't my code, however will do what you need,
Code:
--------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' this code will undo PASTE and instead do a PASTE SPECIAL VALUES which will
' allow you to retain FORMATS in all of the cells in all of the sheets, but will
' also allow the user to COPY and PASTE data
Dim UndoString As String
Dim srce As Range
On Error GoTo err_handler
UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then
Exit Sub
End If
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Undo
If UndoString = "Auto Fill" Then
Set srce = Selection
srce.Copy
Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.SendKeys "{ESC}"
Union(Target, srce).Select
Else
Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
err_handler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

--------------------

I need to make sure that anyone pasting data into a worksheet uses the
paste
special-value method. Is there a way to trap the "paste" execute and
automatically change it to a paste special - values?

Thanks,
- Pat

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Dreiding]

Simon,

Thank you. With slight adjustment I was able to accomplish my goal!

Thanks,
- Pat