vba 在excel中复制粘贴操作时禁用格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12527059/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
disable formatting while copy paste operation in excel
提问by Amit
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.
任何想法将不胜感激。
回答by MikeD
In essence you want to forbid a standard pasteand 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.
如果您喜欢这种方法，我可以进一步详细说明。
