Register now or log in to join your professional community.
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
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
