Excel VBA 如何检测是否在工作表中粘贴了某些内容 [英] Excel VBA How to detect if something was pasted in a Worksheet

问题描述

我首先要说的是，我在 Excel 和 VBA 方面的经验仅限于我在学校看到的情况.我有编程经验，但会使用其他语言.

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.

我每周都会收到一份文件.此文件的结构始终相同:ID、名称、日期、1 到 4 之间的值，非相关数据.

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.

通过全选"按钮(工作表的左上角，MS excel 2013 中单元格名称下方的小三角形)选择此数据，然后复制到另一个默认文件中，该文件重新处理数据以显示和过滤不同的数据基于 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.

我的问题:我如何检测数据何时被粘贴/正在粘贴?我已经尝试过 Worksheet.Change 事件，但粘贴命令 (CTRL+V) 不会触发 Change 事件.另外，如何复制数据?它会逐行更新，逐个单元格(哪个方向)，...?我知道一旦我检测到复制命令，我就可以通过调试轻松找到最后一个问题的答案，但你永远不知道是否有人知道答案.

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.

是否有另一种更简单(或更好)的方法来做到这一点?

Is there another, more easy (or better) way to do this?

如果需要，可以提供更多数据和信息.

More data and information can be given if needed.

感谢您的帮助.

'...已经/正在被复制?'已更改为粘贴.

'...has/is being copied?' changed to pasted as it should've been.

推荐答案

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

这就成功了.对于那些需要类似的东西并且知道他们的列表大小的人@MaciejLos 的回答也适用.

This did the trick. For those who need something similar and know the size of their list @MaciejLos' answer would also work.

这篇关于Excel VBA 如何检测是否在工作表中粘贴了某些内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！