Avatar of terpsichore
 asked on

Need Excel code to track change in a single field

Dear expert!
I urgently need help on this.
I have a spreadsheet; on the first tab, there is a calculated field in cell E28. I want, on a separate tab in the same spreadsheet (let's call it "Changes"), to track the value of this cell along with the date, every time this spreadsheet is saved.
How do I do this? (I found a few similar questions, but didn't have the acumen to extrapolate).

Many thanks!
Visual Basic.NETMicrosoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon

The simplest and quickest way to do this is to actually use the inbuilt "Track Changes" option within excel. Under the tools menu click on "track changes" and set your options up.

I forgot to mention. This will turn your spreadsheet into a shared excel sheet. Make sure you finish building the excel spreadsheet before you turn this on. Once you have it turned on, you can turn on "Protect Shared Workbook" to prevent "sharing with track changes", which prevents the history from being removed.

Also forgot to mention, that when your selecting your "highlight changes" options select the option "list changes on a new sheet". This will list all the changes to a specified range with date, time, who, what type of change, and etc.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Wayne Taylor (webtubbs)

Hi terpsichore,

Paste this code into the worksheet module of the sheet containing the cell to watch (right-click tab > View Code)....

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [E28]) Is Nothing Then Exit Sub
    Dim ChangesWS As Worksheet
    On Error Resume Next
    Set ChangesWS = Worksheets("Changes")
    If Err <> 0 Then
        Set ChangesWS = Worksheets.Add
        ChangesWS.Name = "Changes"
    End If
    On Error GoTo 0
    With ChangesWS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        .Value = Target.Value
        .Offset(0, 1).Value = Now
    End With
End Sub

This will create a new sheet called "Changes" if it does not already exist, and will add the changed value, as well as the date and time.



Dear Wayne --

(First -- to 'theplonk' -- your solution does not appear to work: a) because it does not save changed calculated field values, which is all I need to record; and b) becuase it records every other changed field value - these are many.)

Thanks -- this appears to be the right approach; for some reason it is not "firing" -- I have made it into a module, but the "changes" tab remains blank. Any ideas?
Wayne Taylor (webtubbs)

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

Dear Wayne,

First, you're right, it wasn't in the worksheet module. I moved it but here is the issue: I put a stop on the first line of the routine, and it is not proceeding past the IF statement.
--> I would like only to run this routine when saving/closing the spreadsheet; if any change was made at all to the spreadsheet, I would like to record the value of the field E28, along with the current date. (It is of course possible that it could be saved multiple times on a given date.)

Thanks again,

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.