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!
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
Last Comment
terpsichore
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.
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
.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.
Regards,
Wayne
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
.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.
Regards,
Wayne
ASKER
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?
(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?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
ASKER
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,
Robert
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,
Robert