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

Is it not intersect(target.address, range("E10:E500))? Sorry, just my first feeling. Strange a message box would appear... — Lowpar
– Lowpar, Commented Jul 9, 2017 at 21:12
@Lowpar My guess is that the OP was using a MsgBox to track whether the function was being called, but didn't post that line in the question. (But I could be wrong.) — YowE3K
– YowE3K, Commented Jul 9, 2017 at 21:17
@YowE3K yeah, I just realized the infinite loop was caused by the cell itself changing, causing a recursive function which would consistently repeat the same action. Great solution to disable events! — Lowpar
– Lowpar, Commented Jul 9, 2017 at 21:20

YowE3K · Accepted Answer · 2017-07-09 22:12:15Z

2

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

edited Jul 9, 2017 at 22:12

answered Jul 9, 2017 at 21:15

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tim Williams Over a year ago

Just to note: it's possible that Target could represent >1 cell, or even a whole row/column. In those cases, just because the intersect between Target and a given range is not Nothing does not mean that all of the Target range lies within your range of interest. So checking for an intersect and then going ahead to operate on Target could give unexpected results.

YowE3K Over a year ago

@TimWilliams I had thought of that, but I wasn't sure why the OP was using oldCellValue and oldCellAddress. (Possibly to have the old value and new values available somewhere else in [unposted] code?). Keeping track of "old" values while handling multiple concurrent changes (or even multiple sequential changes) seemed like too much effort for a "disable the Events" answer, but I have added an alternative which handles multiple changes without implying the maintenance of the "old" variables.

Tim Williams Over a year ago

Yes good point - the globals - which are typed as String - would cause an error if multiple cells are updated...

Ehsan Over a year ago

Thank you all, Its working fine now just by disabling and enabling the events properly.

Collectives™ on Stack Overflow

updating value of the Target cell in Excel VBA

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related