在 Excel VBA 中更新目标单元格的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45001033/
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
updating value of the Target cell in Excel VBA
提问by Ehsan
I am trying to update the value of the Target cell using VBA macro when it exists in particular Range. I want to update its value as by concatenating a string to its value. For example if some writes 250 in the Target cell, I want to write back "XYZ-250" into the target cell. Below is the code:
当目标单元格存在于特定范围时，我试图使用 VBA 宏更新目标单元格的值。我想通过将字符串连接到它的值来更新它的值。例如，如果有人在目标单元格中写入 250，我想将“XYZ-250”写回到目标单元格中。下面是代码：
Dim oldCellAddress As String
Dim oldCellValue As String
Private Sub Worksheet_Change(ByVal Target As Range)
oldCellValue = 0
If Not Intersect(Target, Range("E10:E500")) Is Nothing Then
oldCellValue = Target.Value
Sheet1.Cells(Target.Row, Target.Column).Value = "AST-" & oldCellValue
End If
End Sub
Interestingly when I change the value of any cell within the range (E10 to E500) the messagebox is displayed infinite times and halts the excel and I have to restart it again.
有趣的是，当我更改范围（E10 到 E500）内任何单元格的值时，消息框会无限次显示并停止 excel，我必须再次重新启动它。
Thanks in advance
提前致谢
回答by YowE3K
Disable events prior to making any change that will fire the Changeevent:
在进行任何会触发Change事件的更改之前禁用事件：
Dim oldCellAddress As String
Dim oldCellValue As String
Private Sub Worksheet_Change(ByVal Target As Range)
oldCellValue = 0
If Not Intersect(Target, Range("E10:E500")) Is Nothing Then
Application.EnableEvents = False
oldCellValue = Target.Value
Target.Value = "AST-" & oldCellValue
Application.EnableEvents = True
End If
End Sub
If events aren't disabled, your change to the cell will fire the Worksheet_Changeevent, which will change the cell, which will fire the Worksheet_Changeevent, which will change the cell, which will fire the Worksheet_Changeevent, which will change the cell, which will fire the Worksheet_Changeevent, which will change the cell, which will fire the ...
如果事件未禁用，您对单元格的更改将触发Worksheet_Change事件，这将更改单元格，将触发Worksheet_Change事件，这将更改单元格，将触发Worksheet_Change事件，这将更改单元格，将触发的Worksheet_Change情况下，这将改变细胞，这将火...
Assuming you don't need your oldCellValueand oldCellAddressmodule-scope variables, but do want to handle changes to multiple cells, use this instead:
假设您不需要您oldCellValue和oldCellAddress模块范围的变量，但确实想要处理对多个单元格的更改，请改用：
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E10:E500")) Is Nothing Then
Dim c As Range
Application.EnableEvents = False
For Each c In Intersect(Target, Range("E10:E500")).Cells
c.Value = "AST-" & c.Value
Next
Application.EnableEvents = True
End If
End Sub
