vba VBA代码只允许在excel模板中特殊粘贴
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5228987/
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
VBA Code to allow only paste special in the excel template
提问by Sangram Nandkhile
HI,
你好，
I am using an Excel template with columns having different formulas and format, I don't want those formats to be replaced when i paste something from another excel sheet.
我正在使用一个 Excel 模板，其中的列具有不同的公式和格式，当我从另一个 Excel 工作表粘贴某些内容时，我不希望这些格式被替换。
Can anybody provide me, just a VBA code which i will paste in the VBA editor and all the paste operation in my excel template will be PASTE SPECIAL ?
任何人都可以提供给我，我将粘贴到 VBA 编辑器中的 VBA 代码，我的 excel 模板中的所有粘贴操作都将是 PASTE SPECIAL 吗？
回答by MP?kalski
You should try to use Events in your workbook. Sorry if my translations are not in line with yours Excel, but I have Polish version of MS Office. However, if you press CTRL+F11 and open VB Editor in the Project Pane you should have something like "Microsoft Excel Objects" and there objects corresponding to Spreadsheets and Workbook. Now, in ThisWorkbookplace a code
您应该尝试在工作簿中使用事件。抱歉，如果我的翻译与您的 Excel 不一致，但我有波兰语版的 MS Office。但是，如果您按 CTRL+F11 并在项目窗格中打开 VB 编辑器，您应该有类似“Microsoft Excel 对象”的内容以及与电子表格和工作簿对应的对象。现在，在ThisWorkbook 中放置一个代码
Option Explicit
Private Sub WorkBook_Open()
MsgBox "this happens when workbook is opened"
Application.OnKey "^v","my_function"
End Sub
This code will override the CTRL+V and run my_function() when this key combination is pressed. Given the function above you may now insert new module and add there a sub e.g.
当按下此组合键时，此代码将覆盖 CTRL+V 并运行 my_function()。鉴于上述功能，您现在可以插入新模块并添加一个子模块，例如
Option Explicit
Sub my_function()
MsgBox "you have pressed ctrl+v"
Selection.PasteSpecial Paste:=xlPasteForumlas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
For more information about the events see e.g. http://www.cpearson.com/excel/Events.aspx
有关事件的更多信息，请参见例如http://www.cpearson.com/excel/Events.aspx
If the code above does not work check the value of Application.EnableEvents, and if neccessary change it's value in immediate window.
如果上面的代码不起作用，请检查 Application.EnableEvents 的值，并在必要时更改它在即时窗口中的值。
Moreover, in Sheet1you may try to do something with change event. Using this event you may prevent excel from changing the styles, although the first solution should be esier to implement. Below is the code for the change event.
此外，在Sheet1 中，您可以尝试使用 change 事件做一些事情。使用此事件可能会阻止 excel 更改样式，尽管第一个解决方案应该更容易实现。下面是更改事件的代码。
Option Explicit
Private Sub WorkSheet_Change(ByVal Target As Range)
MsgBox "the value was changed"
End Sub
Hope it helped.
希望它有所帮助。
Some links on events in Excel
Excel 中有关事件的一些链接
