Problem with Time Stamp in comments...

**corty_d** · 11-07-2008, 09:37 AM

I have been able to use a time stamp code to put a date and time in a cell... I then worked out how to use this to list dates and times in the same cell (instead of replacing the cells contents)... using this code:

ActiveCell.Value = ActiveCell.Value & Chr(10) & Format(Date + Time, "dd/mm/yyyy  hh:mm")

Now i can use the time stamp to input into a comments box using this code:

ActiveCell.AddComment.Text Format(Date + Time, "dd/mm/yyyy  hh:mm")

But i cannot get it to list the dates in the comments box each time i stamp rather than replace the existing comments... Can anyone help?

**royUK** · 11-07-2008, 10:29 AM

You now have 3 threads with this question! Have you read the Forum Rules, especially about duplicate posts?

**Rick_Stanich** · 11-07-2008, 10:34 AM

I found some code and modified it for your code, is this what you want?

Sub AddOrAppendComment()
Dim sMyComment As String
Dim sExisting As String

    ActiveCell.Value = ActiveCell.Value & Chr(10) & Format(Date + Time, "dd/mm/yyyy  hh:mm")
    'Const sMyComment As String = "My Comment"
    
    With ActiveCell
        If .Comment Is Nothing Then
        .AddComment.Text Format(Date + Time, "dd/mm/yyyy  hh:mm")
            '.AddComment sMyComment
        Else
            sMyComment = ActiveCell.Comment.Text
            'MsgBox sMyComment
            sExisting = .Comment.Text
            .Comment.Delete
            .AddComment sExisting & ", " & sMyComment
            '.Comment.Shape.TextFrame.AutoSize = True 'For auto size of comment box
        End If
    End With
End Sub

**corty_d** · 11-07-2008, 10:43 AM

Ok... almost there. It does add the text to the comment box without deleting it but i dont want it to add any text to the cell itself...

**Rick_Stanich** · 11-07-2008, 11:34 AM

Remove this line:

ActiveCell.Value = ActiveCell.Value & Chr(10) & Format(Date + Time, "dd/mm/yyyy  hh:mm")

I thought you were appending the contents of the cell and comment.

**corty_d** · 11-07-2008, 11:42 AM

Yep i found that... the trouble is... when i run it the second time... all it is doing is duplicating whats in the comment... not adding a new date and time. For example if there is 07/11/2008 12.45 in the comment box... if i click it again it just duplicates it rather than adding the new date and time. Is this a fault with the code?

**Rick_Stanich** · 11-07-2008, 12:14 PM

Oops, I was testing the appending, not the actual content.
Try this:

Sub AddOrAppendComment()
Dim sMyComment As String
Dim sExisting As String

    'Const sMyComment As String = "My Comment"
    
    With ActiveCell
        If .Comment Is Nothing Then
        .AddComment.Text Format("Date + Time" & Now, "dd/mm/yyyy  hh:mm")
            '.AddComment sMyComment
        Else
            sMyComment = "Date + Time, " & Now
            'MsgBox sMyComment
            sExisting = .Comment.Text
            .Comment.Delete
            .AddComment sExisting & ", " & sMyComment
            '.Comment.Shape.TextFrame.AutoSize = True 'For auto size of comment box
        End If
    End With
End Sub