vba 复制粘贴不会触发 worksheet_change
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16049217/
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
Copy-paste doesn't trigger worksheet_change
提问by flamingo
Here is what I need to do: When I have written something into a cell in the sheet, my Worksheet_Changecode should check if the cell contains certain characters and then replace those characters. That part of the code is working fine.
这是我需要做的：当我在工作表中的单元格中写入一些内容时，我的Worksheet_Change代码应该检查该单元格是否包含某些字符，然后替换这些字符。代码的那部分工作正常。
However there is a slightly odd behavior. Here's the code so far:
然而，有一个稍微奇怪的行为。这是到目前为止的代码：
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = Replace(Target.Value, "??", "?")
MsgBox "This is the value: " & Target.Value
End Sub
What's happening is that when I have the characters that need to be changed in my clipboard (using Ctrl+C). When I double-click onto the cell, paste the characters into the cell using Ctrl+V, and then press Enter, the code works just fine, the characters are changed. (Note: Without the double-click, you can't see the cursor.)
发生的事情是，当我的剪贴板中有需要更改的字符时（使用Ctrl+ C）。当我双击单元格时，使用Ctrl+将字符粘贴到单元格中V，然后按 Enter，代码工作正常，字符已更改。（注意：没有双击，您将看不到光标。）
However, if I just go to the cell with my arrow keys and paste over whatever else is in the cell, nothing happens. I suspect the Worksheet_Changeisn't even triggered, or else it would at least display my MsgBox.
但是，如果我只是使用箭头键转到单元格并粘贴单元格中的任何其他内容，则不会发生任何事情。我怀疑Worksheet_Change它甚至没有被触发，否则它至少会显示我的MsgBox.
(I don't know if it's relevant to this, but i am using Excel 2010 in a Mac)
（我不知道它是否与此相关，但我在 Mac 中使用 Excel 2010）
回答by unique2
You can use Worksheet_Calculate on a trigger sheet.
您可以在触发器表上使用 Worksheet_Calculate。
- Create a new sheet which only contains a link to the range you want to watch (i.e. =Sheet1!A1:B3)
- Add a macro called Worksheet_Calculate to your trigger sheet
- Now whenever any date in the linked range changes a recalculation of your trigger sheet gets triggered which in turn sets off your macro
- 创建一个仅包含指向您要观看的范围的链接的新工作表（即 =Sheet1!A1:B3）
- 将名为 Worksheet_Calculate 的宏添加到您的触发器表
- 现在，每当链接范围内的任何日期发生变化时，都会触发触发表的重新计算，从而触发您的宏
This also works when the user enters a formula in the corresponding cell and one of the predecessors changes.
当用户在相应的单元格中输入公式并且前任之一发生更改时，这也适用。
With manual calculation the recalculation of the trigger sheet and the macro only happen when the user presses F9.
使用手动计算，触发器表和宏的重新计算仅在用户按下 F9 时发生。
回答by Santosh
Always use EnableEvents property when working with Worksheet_Change event. For more details link
使用 Worksheet_Change 事件时始终使用 EnableEvents 属性。欲知更多详情 链接
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo err_rout
Application.EnableEvents = False
If Target.Count > 1 Then Exit Sub
Target.Value = Replace(Target.Value, "??", "?")
MsgBox "This is the value: " & Target.Value
err_rout:
Application.EnableEvents = True
End Sub
回答by Albert
If Target.Column = 3 Then
Dim startRow As Integer
Dim endRow As Integer
startRow = Target.Row
endRow = Target.Row + Target.Count - 1
For i = startRow To endRow
' blah blah blah
maybe it helps
也许它有帮助
