How do you trap the paste event in a worksheet?
 

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

How do you trap the paste event in a worksheet?
 

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("&Und o").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

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


Dreiding;214545 Wrote:
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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=58904

How do you trap the paste event in a worksheet?
 

Simon,

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

Thanks,
- Pat

"Simon Lloyd" wrote:


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("&Und o").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

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


Dreiding;214545 Wrote:
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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=58904