disable formatting while copy paste operation in excel

Question

I'm come around with the following code to disable to formatting while cell copy paste operation-

Private Sub Worksheet_Change(ByVal Target As Range)
    With Application
        .EnableEvents = False
        myValue = Target.Formula
        .Undo
        Target.Formula = myValue
        .EnableEvents = True
    End With
End If
    Application.CutCopyMode = False
End Sub

Code works perfect but it insert many other issues in the sheet.

1. Not able to use the undo/redo functionality
2. Not able to change the focus of cell in single click.

Any idea would be appreciated.

Answer 1

In essence you want to forbid a standard paste and possibly replace it by a paste special / values

You could trap the Paste function and assign a Message telling the user to use Paste Special / Values, like in

....
' place this in any suitable event trigger like 
Application.CommandBars("Edit").Controls("Paste").OnAction = "TrappedPaste"
....

Sub TrappedPaste()
    MsgBox "Your Paste is performed as PasteSpecialValues", vbOKOnly, "Paste"

    ' ok, now silently do a PasteSpecial/Values
    On Error GoTo TryExcel
    ' try to paste text
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
    Exit Sub
TryExcel:
    On Error GoTo DoesntWork
     Selection.PasteSpecial xlPasteValues
     Exit Sub
DoesntWork:
    MsgBox "Sorry - wrong format for pasting", vbExclamation + vbOKOnly, "Paste Problem"
 End Sub

Carefull ... this doesn't work in all languages, so for international applications we need to be more subtile

If ExistControl("Edit", 22) Then Application.CommandBars("Edit").FindControl(ID:=22).OnAction = "TrappedPaste"

And there are more places in the application where the user could get a "Paste" from, you need to trap them all.

I can elaborate further if you like the approach.