Excel VBA - 更改单元格时运行宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19146149/
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 - Run a macro when a cell is changed
提问by Kes Perron
I am trying to write a macro that runs automatically any time a sheet is edited. Column H has the heading "Updated on" and the macro should put today's date in cell H# where # is the row of the cell that was changed. Here's the code I used:
我正在尝试编写一个在编辑工作表时自动运行的宏。H 列的标题为“更新于”，宏应该将今天的日期放在单元格 H# 中，其中 # 是已更改的单元格所在的行。这是我使用的代码：
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Select
Range("H" & ActiveCell.Row).Select
ActiveCell.Value = Date
End Sub
After saving the workbook and changing the value of cell A2, the code put today's date into H2 as I expected, but then gave me an error. I clicked debug, and the Target.Selectline was highlighted. I assumed that looping was the problem, so I updated the code:
保存工作簿并更改单元格 A2 的值后，代码按预期将今天的日期放入 H2，但随后出现错误。我单击了调试，该Target.Select行被突出显示。我认为循环是问题所在，所以我更新了代码：
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Select
Range("H" & ActiveCell.Row).Select
ActiveCell.Value = Date
Application.EnableEvents = True
End Sub
This time, I changed the value of cell B3, and it put today's date into B4. Then Excel partially froze: I could still edit that workbook, but I couldn't open or view any other workbook. I closed all the workbooks, but then Excel itself would not close and I had to use the Task Manager to end it.
这一次，我更改了单元格 B3 的值，并将今天的日期放入 B4。然后 Excel 部分冻结：我仍然可以编辑该工作簿，但无法打开或查看任何其他工作簿。我关闭了所有工作簿，但 Excel 本身不会关闭，我不得不使用任务管理器来结束它。
回答by Bathsheba
Using
使用
Private Sub Worksheet_Change(ByVal Target As Range)
Range("H" & Target.Row).Value = Date
End Sub
will give you better stability. Targetis the range that's changed.
会给你更好的稳定性。Target是改变的范围。
It's just possible (I'm at home so can't check) that changing the value re-fires the Worksheet_Change event. If so, then block the recursion with
更改值可能会重新触发 Worksheet_Change 事件（我在家所以无法检查）。如果是这样，则使用
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("H" & Target.Row).Address Then
Range("H" & Target.Row).Value = Date
End If
End Sub
回答by Tim Williams
Private Sub Worksheet_Change(ByVal Target As Range)
const DATE_COL as long = 8
Dim c as range
Set c = Target.Cells(1)
If c.Column = DATE_COL Then Exit Sub
On Error Goto haveError
Application.EnableEvents=False
Me.Cells(c.Row, DATE_COL).Value = Date
haveError:
Application.EnableEvents=True
End Sub
