VBA to keep a running log of changes to specific cell

[pmcgrath92]

Hello all,

As per subject, I am using a basic VBA code which tracks changes in particular cell(s) - in this example, let's say it is A1. As a preamble: I'm aware that using Tracked Changes could achieve the same result; however (without going into too much detail), this solution works best.

I'm using the following code (adapted for cell A1):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strAddress As String
    Dim val
    Dim dtmTime As Date
    Dim Rw As Long
  
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    dtmTime = Now()
    val = Target.Value
    strAddress = Target.Address
  
    Rw = Sheets("Log Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
    With Sheets("Log Sheet")
        .Cells(Rw, 1) = strAddress
        .Cells(Rw, 2) = val
        .Cells(Rw, 3) = dtmTime
    End With
End Sub

As above, this will log the cell where the value has changed (in this case, $A$1), the value (let's say it changed from YES to NO) & the time of the change - into a sheet named "Log Sheet".

Now, the problem:
This works fine when changes in A1 are typed changes - like the above example, of changing from "YES" to "NO", the sheet would log "NO".
However, the issue arises when the change is a VALUE change as a result of calculation - for example, let's say A1 = B1+C1; if I change the values in B1 and C1, then of course the value of A1 changes; however, since there was no change to the CELL itself (i.e. A1 still equals B1+C1), then there is nothing logged. That is to say, any change in the result of the calculation does not log (i.e. if A1 changed from =4 to =5 as a result of changes to B1 and/or C1), since there was no change to A1=B1+C1.
This also remains true for IF functions - i.e. if A1 cell value changes as a result of an IF function based on other cells, it will not be logged; since the IF function itself did not change.

Therefore, the question:
How can I adapt my VBA in order to log changes in the cell value as opposed to just the cell input?

Thanks in advance!

 

[Joe4]

That is correct. A Worksheet_Change event only captures MANUAL changes made to cells, not values that change as the result of a formula or link.
There is a Worksheet_Calculate events, which fires when a re-calculation happens. It seems like that may be a good solution, but it has one major detail which prevents it from being the answer.
Unlike Worksheet_Change, the Worksheet_Calculate can ONLY identify that SOME cell SOMEWHERE on the sheet was re-calculated. It CANNOT identify which cell was re-calculated.
It has no way of knowing if the value in cell A1 changed, or some other value somewhere else on the sheet changed.
So using the Worksheet_Calculate event will not help you here.

However, there may be a way around your conundrum. If you want to identify a change to cell A1, and the formula in A1 is =B1+C1, then we may be able to still use a Worksheet_Change event, but instead watch cells B1 and C1 for changes. As long as those cells are being manually updated (and in turn, updating cell A1), we can watch those cells for manual changes.

In that case, your code would change like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strAddress As String
    Dim val
    Dim dtmTime As Date
    Dim Rw As Long
 
    If Intersect(Target, Range("B1:C1")) Is Nothing Then Exit Sub
    dtmTime = Now()
    val = Range("A1").Value
    strAddress = Range("A1").Address
 
    Rw = Sheets("Log Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
    With Sheets("Log Sheet")
        .Cells(Rw, 1) = strAddress
        .Cells(Rw, 2) = val
        .Cells(Rw, 3) = dtmTime
    End With
End Sub

 

[pmcgrath92]

Hey Joe4,

Thanks for the answer! Unfortunately it doesn't quite work - but I understand why, based on your explanation. The A1=B1+C1 was merely just an example for simplicity - in reality, these are also calculated values. Again, without bogging down with details, I have some cells which are reading external software, and then all other cells are calculated - meaning, in short, there are no cells which are being manually updated, unfortunately. Close, but no cigar!

I'll have a think about how I could adapt - appreciate the further explanation though, good to know.

 

[Joe4]

Yeah, it can get a bit tricky.
I have some people get quite creative in trying to do something like you describe.
One way would be to have a Worksheet_Calculate event that does 2 things:
1. every time it fires (runs), it stores the value of the cell A1 in another spot (i.e. so you to be used as "previous value" in a future check)
2. compares the current value of cell A1 to the stored "previous value", and if they are different, make your time stamp updates

So you may be able to try to approach it that way.

 

[Joe4]

Here is a real simple example to show what I mean. The following code will use cell Z1 to store the previous value of cell A1.
It then compares A1 to Z1. If they are different, it means the value in cell A1 has changed, and it will update the time stamp in cell D1.

I think you may be able to adapt this logic for you needs:

Private Sub Worksheet_Calculate()

'   Compare values in cell A1 to value in cell Z1 to see if it changes
    If Range("A1").Value <> Range("Z1").Value Then
'       If different, update time stamp in cell D1
        Application.EnableEvents = False
        Range("D1") = Now()
'       Copy new value to previous value cell
        Range("Z1").Value = Range("A1").Value
        Application.EnableEvents = True
    End If

End Sub

 

[pmcgrath92]

Hey Joe,

Yes - seems like that might work if I extend to my needs. As you can see from the original VBA I posted, it didn't just post the date into a cell - it kept a running log tabulated in another sheet - I believe the above would simply just update the time/date in cell D1 to the time/date of change; whereas I'm looking to keep a running log of ALL changes over the history of the workbook.

Another slight complication is that it isn't just one cell I'm looking to monitor, but multiple - however I can just extend your VBA above to include additional cells and create additional reference/lookup cells to check for match.

 

[Joe4]

Yes - seems like that might work if I extend to my needs. As you can see from the original VBA I posted, it didn't just post the date into a cell - it kept a running log tabulated in another sheet - I believe the above would simply just update the time/date in cell D1 to the time/date of change; whereas I'm looking to keep a running log of ALL changes over the history of the workbook.

Yes, I just made the "proof of concept" as simple as possible to demonstrate the method. You can write as much as you like wherever you like. It should just be a matter of replacing the part where I am writing to cell D1 with your logic of what to write and where.

And note that you do not have to use cell Z1 to track the previous values of cell A1. You can pick/use any unused cell on your sheet, if you want to push it out further. You can even "hide" that column so the user won't see it.

Another slight complication is that it isn't just one cell I'm looking to monitor, but multiple - however I can just extend your VBA above to include additional cells and create additional reference/lookup cells to check for match.

Yep, you would just repeat add multiple sections within that same "Worksheet_Calculate" event procedure for the different cells that you want to monitor.
You obviously will want to use a different cell to store the previous version (so that each cell being monitored has their own "previous value" cell).

 

[pmcgrath92]

Hey Joe4,

Apologies for the delay, just got round to implementing your suggestion. I went with the following adapted code:

Private Sub Worksheet_Calculate()

    Dim strAddress As String
    Dim val
    Dim dtmTime As Date
    Dim Rw As Long

    If Range("D3").Value <> Range("D94").Value Then
        Application.EnableEvents = False
        
        dtmTime = Now()
        val = Range("D3").Value
        strAddress = Range("D3").Address
    
    Rw = Sheets("Log Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
    With Sheets("Log Sheet")
        .Cells(Rw, 1) = strAddress
        .Cells(Rw, 2) = val
        .Cells(Rw, 3) = dtmTime
    End With
        
        Application.EnableEvents = True
    End If
    

End Sub

Just tried function testing, by changing the value in D94 (note: I don't need the new value of D4 to copy to D94, as per your code), but unfortunately to no avail. I fear I may be missing something obvious in the way the code is written (not an experienced VBA writer/user) - wondering if you can spot anything? Hopefully can have it working for this set of cells; then I can go on and extend to the further cells I'd like to monitor also.

 

[Joe4]

At the end of the code, you need to log the new current value in cell D94 for your next comparison.

Note it was this part of my original code:

'       Copy new value to previous value cell
        Range("Z1").Value = Range("A1").Value

 

[pmcgrath92]

Hey Joe4,

Sorry - I mentioned this in my response: I don't require the new value to copy over to Z1. In short: I am pulling data from an external source, and therefore rather than copy current cell value [A1] to the "comparison" cell value [Z1], I have current external data [D3] comparing the data value from Xmins ago [D94] in my example. I figured, having the ability to do so, would make it simpler, rather than copying the A->Z value over everytime.

In any case, neither is providing any log in my "Log Sheet" tab.