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

 

[paulgu]

Hi Pat,
The following code works in XL 2003:
Put this in your Workbook activate event:
Application.OnKey "^v", "Protection_Formatting.sPaste"
Application.OnKey "+{INSERT}", "Protection_Formatting.sPaste"
CommandBars("Edit").Controls("Paste").OnAction = "sPaste"
CommandBars("Worksheet Menu Bar").Controls("Edit").CommandBar.Controls("Paste").OnAction = "sPaste"
CommandBars("Cell").Controls("Paste").OnAction = "Protection_Formatting.sPaste"
CommandBars("Standard").Controls("Paste").Enabled = False

and this is the function:
Function sPaste()
'This code pastes the format and value of the current selection,to the active range.
On Error Resume Next
Selection.PasteSpecial xlPasteValues
End Function

In your workbook deactivate event put:
With Application
.OnKey "^v"
.OnKey "+{INSERT}"
.OnKey "~"
End With
CommandBars("Edit").Controls("Paste").Reset
CommandBars("Worksheet Menu Bar").Controls("Edit").CommandBar.Controls("Paste").Reset
CommandBars("Cell").Controls("Paste").Reset
CommandBars("Standard").Controls("Paste").Enabled = True
HTH
Paul

 

[Dreiding]

Simon,

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

Thanks,
- Pat