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.

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

Any idea would be appreciated.

You have an `EndIf` in the code. A typo I guess? Are you trying to copy paste just values? If yes, then see this link http://www.siddharthrout.com/2011/08/15/vba-excelallow-paste-special-only/ — Siddharth Rout, Sep 21 '12 at 08:54
Issue 1. See here http://stackoverflow.com/questions/7798575/undo-history-button-clear-after-run-macro-excel — Alistair Weir, Sep 21 '12 at 09:06

score 0 · Answer 1 · answered Sep 21 '12 at 16:07

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.

disable formatting while copy paste operation in excel

1 Answers1