Date stamped comment box

[Windward]

I need the date the comment was created to appear next to the name of
the person that inserted the comment to the cell. Is there any easy way
to do this?

[Tom Ogilvy]

There is no setting for this.


There is no add comment event - so not sure how code would do it except
perhaps through brute force; looping through comments and identifying those
which don't contain dates and adding the date using some reliable trigger
like calculate or selection change.

--
Regards,
Tom Ogilvy


"Windward" <[email protected]> wrote in message
news:[email protected]...
> I need the date the comment was created to appear next to the name of
> the person that inserted the comment to the cell. Is there any easy way
> to do this?
>

[Windward]

I think there is a missunderstanding, I don't need to go through
existing comments & find out when they were created (although that
would be nice)

I just want all new comments to automatically input the date next to
the name of whoever is creating the comment.

Thanks,
Josh

[Tom Ogilvy]

No, there was no misunderstanding.

--
Regards,
Tom Ogilvy

"Windward" <[email protected]> wrote in message
news:[email protected]...
> I think there is a missunderstanding, I don't need to go through
> existing comments & find out when they were created (although that
> would be nice)
>
> I just want all new comments to automatically input the date next to
> the name of whoever is creating the comment.
>
> Thanks,
> Josh
>

[STEVE BELL]

The only way I can imagine this being done is using code to create all
comments...

--
steveB

Remove "AYN" from email to respond
"Windward" <[email protected]> wrote in message
news:[email protected]...
>I think there is a missunderstanding, I don't need to go through
> existing comments & find out when they were created (although that
> would be nice)
>
> I just want all new comments to automatically input the date next to
> the name of whoever is creating the comment.
>
> Thanks,
> Josh
>

[Dave Peterson]

You could give the users a macro:

Option Explicit
Sub testme01()

Dim myText As String

myText = InputBox(Prompt:="What's the comment?")

If Trim(myText) = "" Then
Exit Sub
End If

myText = Application.UserName & vbLf & Format(Date, "mm/dd/yyyy") _
& vbLf & myText

With ActiveCell
If .Comment Is Nothing Then
'do nothing
Else
.Comment.Delete
End If
.AddComment Text:=myText
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Windward wrote:
>
> I need the date the comment was created to appear next to the name of
> the person that inserted the comment to the cell. Is there any easy way
> to do this?

--

Dave Peterson

[Windward]

Thanks Dave, that's awesome! If used on a cell that already has a
comment. is it possible to keep the original comment and just edit it
with a new date stamped comment?

Thanks again

[Dave Peterson]

There's lots of ways you can type a date. But this could give you some ideas:

Option Explicit
Sub testme01()

Dim myText As String
Dim ColonPos As Long

With ActiveCell
If .Comment Is Nothing Then
myText = InputBox(Prompt:="What's the comment?")
If Trim(myText) = "" Then
'do nothing
Else
myText = Application.UserName & vbLf _
& Format(Date, "mm/dd/yyyy") _
& vbLf & myText
.AddComment Text:=myText
End If
Else
myText = .Comment.Text
If myText Like "*##/##/####*" _
Or myText Like "*#/#/####*" Then
'do nothing, it has a date
Else
ColonPos = InStr(1, myText, ":", vbTextCompare)
If ColonPos > 0 Then
myText = Left(myText, ColonPos) & " " _
& Format(Date, "mm/dd/yyyy") & " " _
& Mid(myText, ColonPos + 1)
End If
.Comment.Text myText
End If
End If

End With
End Sub



Windward wrote:
>
> Thanks Dave, that's awesome! If used on a cell that already has a
> comment. is it possible to keep the original comment and just edit it
> with a new date stamped comment?
>
> Thanks again

--

Dave Peterson