Create a comment when a cell is changed . . .

Posted by JAF on December 30, 1999 8:18 AM

I have a piece of code which generates a comment in a cell whenever that cell is changed (see below), 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 &LT;&GT; Target.Address Then Exit Sub
If Target.Value = "" Then
acVal = ""
Else
acVal = Target.Value
End If
End Sub

Posted by Tom Morales on December 30, 1999 12:11 PM

JAF: 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

Tom

Posted by Tom Morales on December 30, 1999 12:13 PM

The historical data sheet will get filled in, as work on your original sheet progresses. You could embellish the code by hiding and unhiding the sheet, as necessary.
Tom