reubendayal
04-15-2014, 03:24 AM
Hi All,
I work on a sheet all day and track my work from it. Each case I work on I have a row dedicated to it, similarly there are respective columns related to each case. I have a column that tracks the latest transaction I've made on a particular case. And for now I simply use F2 and add in the comments each time, this leads to a lot of text being added to the same field and if I choose to wrap text the whole sheet ends up looking ugly. Anyway, what I need help with is to create a VBA code that will show the previous updates I had made to each field (as a comment over that field) in the 'Last Transaction' column.
I have come across another code that does this, but it simply tracks changes to ever single cell each time and posts comments. This is quite annoying as every small change I make anywhere in the sheet gets noted. I only need this noting to be made to the 'Last Transaction' column.
This post was by another member on -
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
Many thanks in advance.
Reuben
I work on a sheet all day and track my work from it. Each case I work on I have a row dedicated to it, similarly there are respective columns related to each case. I have a column that tracks the latest transaction I've made on a particular case. And for now I simply use F2 and add in the comments each time, this leads to a lot of text being added to the same field and if I choose to wrap text the whole sheet ends up looking ugly. Anyway, what I need help with is to create a VBA code that will show the previous updates I had made to each field (as a comment over that field) in the 'Last Transaction' column.
I have come across another code that does this, but it simply tracks changes to ever single cell each time and posts comments. This is quite annoying as every small change I make anywhere in the sheet gets noted. I only need this noting to be made to the 'Last Transaction' column.
This post was by another member on -
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
Many thanks in advance.
Reuben