Excel VBA 如何检测是否在工作表中粘贴了某些内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27818152/
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
Excel VBA How to detect if something was pasted in a Worksheet
提问by Nahbyr
I'll start by saying that my experience with Excel and VBA is limited to what I saw in school. I have programming experience, but in other languages.
我首先要说的是，我在 Excel 和 VBA 方面的经验仅限于我在学校看到的。我有编程经验，但使用其他语言。
I have a file that I get every week. The structure of this file is always the same: ID, Name, Date, Value between 1 and 4, non-relevant data.
我每周都会收到一份文件。此文件的结构始终相同：ID、名称、日期、1 到 4 之间的值、非相关数据。
This data is selected through the 'select all' button (top left corner of the worksheet, little triangle below the cellname in MS excel 2013) and then copied into another default file that reworks the data to show and filter it in different sheets based on the 1-4 value and the date.
这些数据是通过“全选”按钮（工作表的左上角，MS excel 2013 中单元格名称下方的小三角形）选择的，然后复制到另一个默认文件中，该文件重新处理数据以在不同的工作表中显示和过滤数据1-4 值和日期。
My question:How do I detect when data has/is being pasted? I've tried the Worksheet.Change event, but the paste command (CTRL+V) does not trigger the Change event. Also, how will the data be copied? Will it update Row by row, cell by cell (which direction), ...? I know I can easily find the answer to the last question by debugging it once I can detect the copy command, but you never know if someone knows the answer.
我的问题：如何检测何时粘贴/正在粘贴数据？我已经尝试过 Worksheet.Change 事件，但粘贴命令 (CTRL+V) 不会触发 Change 事件。另外，如何复制数据？它会逐行更新，逐个单元格（哪个方向），...？我知道一旦我检测到复制命令，我就可以通过调试轻松找到最后一个问题的答案，但你永远不知道是否有人知道答案。
Is there another, more easy (or better) way to do this?
是否有另一种更简单（或更好）的方法来做到这一点？
More data and information can be given if needed.
如果需要，可以提供更多数据和信息。
Thank you for your help.
感谢您的帮助。
EDIT: '...has/is being copied?' changed to pasted as it should've been.
编辑：“...是否已/正在被复制？” 改为粘贴，因为它应该已经。
回答by Nahbyr
Private Sub Worksheet_Change(ByVal Target As Range)
Dim UndoList As String
'~~> 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" Then
'Do stuff
End If
End Sub
This did the trick. For those who need something similar and know the size of their list @MaciejLos' answer would also work.
这成功了。对于那些需要类似内容并知道列表大小的人来说，@MaciejLos 的答案也适用。
回答by Maciej Los
Worksheet_Changeevent will do the job if you add a formula into cell which will never be overwritten. Let's say your data are pasted into A1 cell and occupied 5 columns. So, enter below formula into 6. column and row 1.
如果将永远不会被覆盖的公式添加到单元格中，Worksheet_Change事件将完成这项工作。假设您的数据粘贴到 A1 单元格并占用 5 列。因此，将以下公式输入到 6. 列和行 1 中。
=COUNTBLANK(A1:A1048576)
Now, you're able to handle/detect paste event ;)
现在，您可以处理/检测粘贴事件；)
