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]

How/when are cells D3 and D94 being updated?

 

[pmcgrath92]

Both are being updated automatically every 5 seconds from the Plug-In; and/or when the sheet recalculates.

 

[Joe4]

If there is a re-calculation, and the values are different, your code should work.
If it does not, then probably one of the following situations exist:
- a re-calculation is not being triggered
- the values are the same when the code is running
- VBA or events are disabled
- you have placed this code in the wrong place (needs to be in the Sheet module of the sheet where D3 and D94 are being update)
- your log sheet is not named exactly "Log Sheet"

You can easily verify the first, third, and fourth points by temporarily adding this line of code after your "Dim" statements:

MsgBox "VBA code is running"

If you put that in there, then you should see that message box pop up every 5 seconds, as the values are being updated.
If not, then you need to verify that VBA and events are enabled, and you have placed the code in the correct sheet module.

 

[pmcgrath92]

So the message box appears - which I assume means the code is running (tested by manually clicking the "Calculate Now" from Formulas>Calculation). This worked fine.

I then manually edited the value of D94 to be different (in an attempt to trigger a log); I received the pop-up message, however after this another dialog box appeared with "Out of Stack Space"... which I would understand if I had ALL of the pairs of checked cells in them; but not sure when it's just D3 vs. D94?

 

[Joe4]

If you have a lot of cells being updated automatically by formulas/links, you are going to be calling this code a lot, and may be overloading the system.
As I mentioned in my first post, there is no way to tell Worksheet_Calculate to only fire when certain cells are updated. It will fire anytime any re-calculation occurs anywhere on the sheet.
If you have a lot of cells on your sheet updating every few seconds, I am thinking trying to use Excel to track the changes may not be a good solution.

 

[pmcgrath92]

Yeah, starting to think that too; although, as per original post, I was avoiding this for other reasons which complicate the conversation...

I tried tweaking a few things, and now no longer get the Stack error; but instead:
Run-time error '-2147417848 (80010108)':
Method 'Range' of object '_Worksheet' failed

When I click Debug, it seems to break at this part of the code (highlight yellow):

Rw = Sheets("Log Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1

 

[Joe4]

Try changing that line to this:

Rw = Sheets("Log Sheet").Range("A" & Sheets("Log Sheet").Rows.Count).End(xlUp).Row + 1

 

[pmcgrath92]

Edit: it now appears to be working! (strange as all I took out was the 'VBA is running' line...)

I think, as a side, based on how many cells are updating (causing sheet to run slow), it may be wise to have Auto Calculate of the worksheet turned off; and therefore only trigger the code/update of the values when I choose (could even use a pushbutton).

Putting code to turn off auto calculate at the top of this sheet should work OK, right?

 

[Joe4]

Putting code to turn off auto calculate at the top of this sheet should work OK, right?

In theory, it should work. Best way to find out is to try it!

 

[pmcgrath92]

All working Thanks for the help!

Final solution, for clarity:

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
        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
    End If
End Sub