PDA

View Full Version : [SOLVED] VBA code for tracking tracking updates in a column



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

Bob Phillips
04-15-2014, 05:59 AM
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 Me.Cells(Target.Row, "Z") '<=== change Z to the actiual column
'get the previous value when value changes
.AddComment
.Comment.Visible = False
.Comment.Text Text:="Previous value = " & Sheet2.Range(Target.Address)
End With
End Sub

reubendayal
04-16-2014, 07:59 AM
Thank you LDX!

This just worked perfectly. :)