Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

How to keep history of original data in cell as a comment added to this cell with date, cell value before amendment, user name wrote that value?

I think this question is difficult to answer for many I dare you to know . . . . . . . . . . . . . . . how to let this cycle works for any number of further amendments to cell contents by adding new history to cell comment (without destroying history kept for earlier amendments) I made it at work and it works great Example: cell value in day1 is :500 and input was made by user: Jerry excel do nothing cell value in day2 is :620 and input was made by user: Tom Excel generates comment for this cell mentions: day1,500, Jerry cell value in day3 is :840 and input was made by user: Mickey Excel keep comment for this cell mentions: day1,500, Jerry and adds additional data to comment for this cell mentions: day2,620, Tom and technique keep works forever

user-image
Question added by emad awad , internal audit manager & cost control manager , nile sugar
Date Posted: 2013/09/24
Zafar Iqbal
by Zafar Iqbal , Teacher (Pak Studies) Subject Specialist , Home Tutor

I think its part of parogramming in Excel if I am not wrong.

Deleted user
by Deleted user

In fact, what you are asking for is a logging, that becomes visible as a comment in the changed cell.

 

But if the cell is changed often, the comment space will not be sufficient, so you have to limit the amout of 'old values', otherwise your script will crash after a while.

 

Next to the User-ID, a time-stamping is important as well. You did not mention that.

 

Also, a VBA script can read the current user, but not the previous user. so every change in the cell has to be logged, only the previous change(s) appear in the comment, up to a certain limit.

Content of log: date/time =NOW(), username (Application.UserName), value (given by user)

 

My method of working would  be:

- create a (hidden) log sheet in the workbook

- trigger a script every time the cell is accessed a script that adds a record with the given values

- only add a log record if the value is changed

- replace the comment field with the previous (for example)10 log records

- keep the full history in the (hidden) log sheet

 

Mahmoud did already a good job in creating a good example of a VBA script. If you want I can give my version of the script as  well.

Mahmoud Aun
by Mahmoud Aun , Office Manager [Accompanying Office] , His Excellency Sheikh Saleh Kamel

I have a piece of code which generates a comment in a cell whenever that cell is changed, but I need to "tweak" it slightly.

The changes I need are as follows:

1) Do not create the comment when data is first input in the cell. Only create it when an existing value is changed.

2) Is it possible to amend the code to keep a history of the changesmade to the cell? In other words, each time the value is changed, the comment is added to rather than being owerwritten.

Current Macro Code:

Public acVal

-------------------------------------------------------

Private Sub Workbook_SheetChange(ByVal Sh As Object, _

ByVal Target As Excel.Range)

On Error Resume Next

Target.AddComment

Target.Comment.Text "Modified on " & Date & " by " & _

Application.UserName & ". Previous value was " & acVal

End Sub

-------------------------------------------------------

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _

ByVal Target As Excel.Range)

If ActiveCell.Address <> Target.Address Then Exit Sub

IfTarget.Value = "" Then

acVal = ""

Else

acVal = Target.Value

End If

End Sub

---------------------------

 

ِAlso, The following should work if you create a new sheet named "historical data":

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error Resume Next

here = Target.Address

newnum = Target.Value

oldnum = Sheets("historical data").Range(here).Value

If oldnum = "" Then GoTo line99

oldcomment = Target.Comment.Text

Target.AddComment

Target.Comment.Text oldcomment & "Modified on " & Date & " by " & _

Application.UserName & ". Previous value was " & oldnum & Chr(10)

line99:

Sheets("historical data").Range(here).Value = newnum

End Sub

Syed Hussaini
by Syed Hussaini , Senior Design engineer , private

It is very simple Just enable Track-changes option, which would track all the changes made to the cells which has been selected for track changes option.in an excel sheet 

SIVADASAN PANTHEERADI
by SIVADASAN PANTHEERADI , Sr ADMN ( Actively looking for a NEW JOB) , DYNCORP INTERNATIONAL LLC (US COMPANY)

I think now you cleared your doubt by getting valuable answers by muhammed.

Abdelmonaam Kallali
by Abdelmonaam Kallali , Data application developer , DragonWave

This can be done only using VBA, where, on Click, you read your cell value and append it to the comment value of that cell. Obviously you would have to have some checks if the cell is empty or if the last value was already added to the cell and if the comment was already created (and so on).

Hany Hassanein
by Hany Hassanein , Microsoft Trainer , Egyptian Banking Institute

can you make it using tack changes tool

Ajith Mohandas
by Ajith Mohandas , Sales, Tender, Contracts & Proposal executive (Oil & Gas), , PETROSTEM

use tracking mode in excel and word

More Questions Like This