allow paste special values only

[newbievba_]

hi all, i need your help with an error i get when running the vba. what i want is to allow users to paste special values only when they use the ctrl+v shortcut for example. i'm using the code below and i'm getting a runtime error for section highlighted in red. of course when i paste something anywhere in the workbook, it's pasting everything, formats and values.

your help would be appreciated
thank you

Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim UndoList As String

Application.ScreenUpdating = False
Application.EnableEvents = False

On Error GoTo Whoa

'~~> Get the undo List to capture the last action performed by user
UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)

'~~> Check if the last action was not a paste nor an autofill
If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" _
Then GoTo LetsContinue

'~~> Undo the paste that the user did but we are not clearing
'~~> the clipboard so the copied data is still in memory
Application.Undo

If UndoList = "Auto Fill" Then Selection.Copy

'~~> Do a pastespecial to preserve formats
On Error Resume Next
'~~> Handle text data copied from a website
Target.Select
ActiveSheet.PasteSpecial Format:="Text", _
Link:=False, DisplayAsIcon:=False

Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
On Error GoTo 0

'~~> Retain selection of the pasted data
Union(Target, Selection).Select

LetsContinue:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume LetsContinue
Application.ScreenUpdating = True
Application.Calculation = xlCalculationManual
End Sub

 

[RudiS]

I have run the code several times without the identified line giving me errors?
BTW: Is is really necessary to have that line anyway as the range that is being pasted remains selected even without the line.