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 its part of parogramming in Excel if I am not wrong.

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.

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

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 

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

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).

can you make it using tack changes tool

use tracking mode in excel and word