How to run VBA code when cell contents are changed via formula

Question

The code below works fine when I manually update column I. What I need is to know if there is a way to still have this code work when I have column I updated by a formula.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If .Count > 1 Then Exit Sub

        If Not Intersect(Range("I3:I30"), .Cells) Is Nothing Then
            Application.EnableEvents = False
            If IsEmpty(.Value) Then
                .Offset(0, -1).ClearContents
            Else
                With .Offset(0, -1)
                    .NumberFormat = "m/d/yy h:mm"
                    .Value = Now
                End With
            End If

            Application.EnableEvents = True
        End If
    End With
End Sub

In principle this should still work - but note that if more than one cell is changed (which is possible when a formula is involved) your code will return without apparently doing anything because of the `If .Count > 1 Then Exit Sub` statement — Floris, Mar 29 '13 at 01:27
Unfortunately it is not updating correctly. There is one other formula that is a simple divide formula in column G — user2221902, Mar 29 '13 at 01:28
Does the event not get fired? Add a Debug.Print(Target.Address) statement to confirm... It might be instructive. Of course a function in column G will not cause anything useful in your event handler since it checks that the changed cell is in [I3:I30] — Floris, Mar 29 '13 at 01:30
I added it in, event went through fine. If I manually update the cell it will fire fine. — user2221902, Mar 29 '13 at 01:35
I do not know what "event went through fine" means. Did the debug produce output when you used a formula? What was the address it reported? How is it different when you do it manually? I can't read your mind or your screen - only what you write here! — Floris, Mar 29 '13 at 01:37
It didn't output anything, maybe I didn't put it in right. I copied it into the code itself. Was there more I needed to add to it? Sorry, I haven't used vba in a couple years so I'm pretty rusty — user2221902, Mar 29 '13 at 01:40
possible duplicate of [VBA code doesn't run when cell is changed by a formula](http://stackoverflow.com/questions/11406628/vba-code-doesnt-run-when-cell-is-changed-by-a-formula) — Siddharth Rout, Mar 29 '13 at 05:47

chris neilsen · Answer 1 · 2013-03-29T04:06:55.703

Worksheet_Change does not fire in responce to a formula update.

See Excel help for Worksheet_Change

Occurs when cells on the worksheet are changed by the user or by an external link.

You could maybe achieve what you want with the Worksheet_Calculate event.

Assuming you want to put a time stamp next to the cells when those vall values change, try this (in addition to your Change event).

Note the use of the Static variable to track previous values, since Calculate event does nopt provide a Target parameter like Change does. This method may not be robust enough since Static's get reset if you break vba execution (eg on an unhandled error). If you want it more robust, consider saving previous values on another (hidden) sheet.

Private Sub Worksheet_Calculate()
    Dim rng As Range, cl As Range
    Static OldData As Variant

    Application.EnableEvents = False
    Set rng = Me.Range("I3:I30")

    If IsEmpty(OldData) Then
        OldData = rng.Value
    End If

    For Each cl In rng.Cells
        If Len(cl) = 0 Then
            cl.Offset(0, -1).ClearContents
        Else
            If cl.Value <> OldData(cl.Row - rng.Row + 1, 1) Then
                With cl.Offset(0, -1)
                    .NumberFormat = "m/d/yy h:mm:ss"
                    .Value = Now
                End With
            End If
        End If
    Next
    OldData = rng.Value
    Application.EnableEvents = True
End Sub

Update

Tested routine on sample sheet, all works as expected

Sample file contains the same code repeated on 25 sheets, and range to time stamp is 10000 rows long.

To avoid repeating the code, use the Workbook_ events. To minimise run time use variant arrays for the loop.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim rng As Range
    Dim NewData As Variant

    Dim i As Long
    Static OldData As Variant

    Application.EnableEvents = False
    Set rng = Sh.Range("B2:C10000")  ' <-- notice range includes date column
    NewData = rng

    If IsEmpty(OldData) Then
        OldData = rng.Value
    End If

    For i = LBound(NewData, 1) To UBound(NewData, 1)
        If Len(NewData(i, 1)) = 0 And Len(NewData(i, 2)) > 0 Then
             rng.Cells(i, 2).ClearContents
        Else
            If NewData(i, 1) <> OldData(i, 1) Then
                With rng.Cells(i, 2)
                    .NumberFormat = "m/d/yy -- h:mm:ss"
                    .Value = Now
                End With
            End If
        End If
    Next
    OldData = rng.Value
    Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'Activate date population on cell change
    With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Sh.Range("B2:B10000"), .Cells) Is Nothing Then
            Application.EnableEvents = False
            If IsEmpty(.Value) Then
                .Offset(0, 1).ClearContents
            Else
                 'Populate date and time in column c
                With .Offset(0, 1)
                    .NumberFormat = "mm/dd/yyyy -- hh:mm:ss"
                    .Value = Now
                End With
            End If
            Application.EnableEvents = True
        End If
    End With

End Sub

I tried changing it to the Calculate, but I wasn't sure where to put the ByVal Target As Excel.Range since it's not a procedure that matches the event. — user2221902, Mar 29 '13 at 02:06
`Calculate` doesn't provide a `Target` parameter. See update. — chris neilsen, Mar 29 '13 at 02:26
Sorry to be such a pain on this issue, I am just trying to get it solved tonight. Unfortunately, the above good does not update the time stamp either. — user2221902, Mar 29 '13 at 02:46
I've tested this, and it _does_ work (at least on my sample data). There must be more to your sheet than you've posted... — chris neilsen, Mar 29 '13 at 02:48
The code above and your code is the entire code I have for the ICU sheet. I can post a sample if there is a way to do it. — user2221902, Mar 29 '13 at 03:04
Thank you for all the help [link]http://www.4shared.com/office/eCIHKrFL/Book11.html — user2221902, Mar 29 '13 at 03:15
Updated file at [](http://www.4shared.com/office/QhSYSCtx/Book11.html). Code is now located in the `ThisWorkbook` module — chris neilsen, Mar 29 '13 at 04:03
Thank you, sorry again for all the headache. The only question I have is in the above Calculate portion, how would I adjust that if the data was in column c and the time would be put into column b? I tried switching all of the NewData(i, 1) to NewData(i, 2) and vice versa but that didn't do it. I also realized I uploaded the completely wrong book last night but now I am in a position where I cannot upload another one at all. — user2221902, Mar 29 '13 at 11:55
Nevermind, I got the code to work on workbook change. However, I need it to only update specific sheets. I uploaded the wrong book and cannot upload the correct one now. Is there any way to tell it what sheets to update or to adjust it back to a worksheet event? — user2221902, Mar 29 '13 at 12:07

How to run VBA code when cell contents are changed via formula

1 Answers1