deedii
01-31-2012, 03:38 PM
Hi guys Im having a trouble with the track comment I was using from kb repo. I was using this code below on my sheet to track cell changes as comment.
Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Target.ClearComments
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then
preValue = "a blank"
Else: preValue = Target.Value
End If
End Sub
And this one to delete the comment.
Sub clean()
Dim wks As Worksheet
Dim cmnt As Comment
For Each wks In ActiveWorkbook.Sheets
For Each cmnt In wks.Comments
cmnt.Delete
Next cmnt
Next
End Sub
Now my problem is each time i run clean() all comments are being erased. I want only comment created today are the only one to delete not to include past comment that was created. How can I do that? Thank you :)
Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Target.ClearComments
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then
preValue = "a blank"
Else: preValue = Target.Value
End If
End Sub
And this one to delete the comment.
Sub clean()
Dim wks As Worksheet
Dim cmnt As Comment
For Each wks In ActiveWorkbook.Sheets
For Each cmnt In wks.Comments
cmnt.Delete
Next cmnt
Next
End Sub
Now my problem is each time i run clean() all comments are being erased. I want only comment created today are the only one to delete not to include past comment that was created. How can I do that? Thank you :)