Create comment with previous cell contents

[ROBROSSUK]

Hi Everyone

I am new to this so I will try to explain myself as clearly as possible.

I have a spreadsheet that charts deliveries to our customer. The customer who receives copies of this sheet make change to the delivery dates and would like the sheet to track these changes. I have the following code (obtained from this site, many thanks for this) that will insert a comment in the changed cell showing the previous cell value, but I have a couple of problems

1) The cell that changes is usually the date and when this is shown in the comment it is expressed as a number ie. 39448 instead of 1/1/08(I understand this is how excel calculates dates) Is it possible to format the comment to be expressed as a date ie. dd/mm/yy)??

2) If that cell changes again the comment just shows the previous value. Does anyone think it is possible to list all previous changes


Below is the VB code I have. Just paste it into the sheet required

Sorry if I have not explained enough. Please let me know if you need further info and I will expand where possible

Thanks
Rob

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'copy previous value to another sheet
Sheet2.Range(Target.Address) = Target
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'//clearing more than one cell causes an error
On Error Resume Next
'//(can't overwrite an existing comment)
Target.ClearComments
With Target
'get the previous value when value changes
.AddComment
.Comment.Visible = False
.Comment.Text Text:="Previous value = " & Sheet2.Range(Target.Address)
End With
End Sub

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'copy previous value to another sheet
Sheet2.Range(Target.Address) = Target.Text
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Const COMMENT_HEAD As String = "Previous value(s):="
'//clearing more than one cell causes an error
On Error Resume Next
'//(can't overwrite an existing comment)
With Target

Dim OldComment

OldComment = .Comment.Text
.ClearComments

'get the previous value when value changes
.AddComment
.Comment.Visible = False
.Comment.Text Text:=COMMENT_HEAD & Replace(OldComment, COMMENT_HEAD, "") & Sheet2.Range(.Address) & vbNewLine
End With
End Sub

[ROBROSSUK]

Thankyou, you are a star

Works Perfectly