ROBROSSUK
11-05-2008, 05:10 AM
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
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