updating value of the Target cell in Excel VBA

Question

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:

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.

Thanks in advance

Answer 1

Disable events prior to making any change that will fire the Change event:

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_Change event, which will change the cell, which will fire the Worksheet_Change event, which will change the cell, which will fire the Worksheet_Change event, which will change the cell, which will fire the Worksheet_Change event, which will change the cell, which will fire the ...

---

Assuming you don't need your oldCellValue and oldCellAddress module-scope variables, but do want to handle changes to multiple cells, use this instead:

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