bcs01
05-10-2013, 12:59 AM
Hey everyone,
I am working on a VBA Code that is supposed to track the last 5 changes made to cells and displaying them as comments (without having to share the workbook).
I already found a really good Code that displays the last 5 changes made to a cell.
Nevertheless if I open the file again after having saved my changes, the previous comment content is displayed at first, but deleted completely if I edit the cell.
Is there a way Excel always displays the last 5 changes made to the cell without deleting the complete comment content in case someone else makes a change ?
Furthermore, I would like to apply the rule to many cells within one column. For example P9-P25 and Q8-Q38.
So far I got the following Code which is valid for cell P9:
Option Explicit
Dim preValue(5) As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$P$9" Then
preValue(5) = preValue(4)
preValue(4) = preValue(3)
preValue(3) = preValue(2)
preValue(2) = preValue(1)
preValue(1) = preValue(0)
Target.ClearComments
Target.AddComment.Text Text:="Previous Values are " & Chr(10) & preValue(1) & Chr(10) & preValue(2) & Chr(10) & preValue(3) & Chr(10) & preValue(4) & Chr(10) & preValue(5)
Target.Comment.Shape.Height = 100
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$P$9" Then
If Target = "" Then
preValue(0) = "a blank"
Else
preValue(0) = Target.Value
End If
End If
End Sub
Any help is very much appreciated.
Thanks a lot,
Jan
I am working on a VBA Code that is supposed to track the last 5 changes made to cells and displaying them as comments (without having to share the workbook).
I already found a really good Code that displays the last 5 changes made to a cell.
Nevertheless if I open the file again after having saved my changes, the previous comment content is displayed at first, but deleted completely if I edit the cell.
Is there a way Excel always displays the last 5 changes made to the cell without deleting the complete comment content in case someone else makes a change ?
Furthermore, I would like to apply the rule to many cells within one column. For example P9-P25 and Q8-Q38.
So far I got the following Code which is valid for cell P9:
Option Explicit
Dim preValue(5) As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$P$9" Then
preValue(5) = preValue(4)
preValue(4) = preValue(3)
preValue(3) = preValue(2)
preValue(2) = preValue(1)
preValue(1) = preValue(0)
Target.ClearComments
Target.AddComment.Text Text:="Previous Values are " & Chr(10) & preValue(1) & Chr(10) & preValue(2) & Chr(10) & preValue(3) & Chr(10) & preValue(4) & Chr(10) & preValue(5)
Target.Comment.Shape.Height = 100
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$P$9" Then
If Target = "" Then
preValue(0) = "a blank"
Else
preValue(0) = Target.Value
End If
End If
End Sub
Any help is very much appreciated.
Thanks a lot,
Jan