Track and display multiple changes by different users as comment
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
| Attachment | Size |
|---|---|
| ChangeAsComment.xlsm | 15.5 KB |
Last changes automatically shown as comment
Hey Nick,
thanks again for your help. I also found another solution if anyone is interested. Target cells are P9-27 and S11-S18. Those can be adjusted as desired.
Last 5 changes are always shown as comment.
BR,
Jan
Option Explicit
Dim preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tempvalue As Variant
Dim T, ChrCount, trimval1, trimval2 As Long
Dim oComment As Comment
If Not Intersect(Target, Range("P9:P27")) Is Nothing Or Not Intersect(Target, Range("S11:S18")) Is Nothing Then
Set oComment = Target.Comment
If Not oComment Is Nothing Then
tempvalue = oComment.Text
For T = 1 To Len(tempvalue)
If Mid(tempvalue, T, 1) = Chr(10) Then
ChrCount = ChrCount + 1
If ChrCount = 1 Then trimval1 = T
If ChrCount = 2 Then trimval2 = T
End If
Next T
If ChrCount >= 5 Then
tempvalue = Right(tempvalue, Len(tempvalue) - trimval2) & Chr(10) & preValue
Else
tempvalue = Right(tempvalue, Len(tempvalue) - trimval1) & Chr(10) & preValue
End If
End If
Target.ClearComments
Target.AddComment.Text Text:="Previous Values are (Earliest to latest) " & Chr(10) & tempvalue
Target.Comment.Shape.Height = 100
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("P9:P27")) Is Nothing Or Not Intersect(Target, Range("S11:S18")) Is Nothing Then
preValue = Target.Value
End If
End Sub
Jan.. this is not a good
Jan.. this is not a good approach to logging changes..
recommend looking here:
http://excelexperts.com/VBA-Tips-Log-An-Audit-Trail
- logs all changes
Hey Nick, I guess your list
Hey Nick,
I guess your list is a lot more bulletproof. Unfortunately my worksheet is very big with lots of data which would make it a very time consuming process to always check out the log sheet for the changes made. What I need is a quick overview of what had recently been changed in the cell. is that possible to configure via Code ?
Thanks a lot for your help,
Jan
not really... you could add a
not really... you could add a right click menu item that takes you to all the revisions for that cell...
here's how to do it
Add right click menu:
http://excelexperts.com/Add-Custom-Options-to-Right-Click-Menu
..to filter the log for the specific cell, just record a macro to get the autofilter syntax