PDA

View Full Version : Solved: Delete only today comment?



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 :)

p45cal
01-31-2012, 04:07 PM
Sub clean()
Dim wks As Worksheet
Dim cmnt As Comment
For Each wks In ActiveWorkbook.Sheets
For Each cmnt In wks.Comments
If InStr(cmnt.Text, Format(Date, "mm-dd-yyyy")) > 0 Then cmnt.Delete
Next cmnt
Next
End Sub
but it won't restore previous comments.

deedii
01-31-2012, 04:40 PM
Thanks for the quick response. No its ok as long as it doesnt delete comments created in the past days.
Thanks you so much :) x0x0

p45cal
01-31-2012, 04:52 PM
Thanks for the quick response. So it should delete only comment that was created today?Yes.

Shoul i still edit this one "mm-dd-yyyy"? I don't think so. It should be the same date format as in the line:
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")

p45cal
01-31-2012, 04:53 PM
Thanks for the quick response. No its ok as long as it doesnt delete comments created in the past days.
Thanks you so much :) x0x0 Clean doesn't, but you do when you change a cell. and that can't be restored

deedii
01-31-2012, 05:59 PM
I see thanks so much for the help. This is solved.