I am looking for a small piece of code that will format a comment feild in
microsoft excel to start with the current date and time.

this is details of what exactly the code needs to do:

1.Insert Comment
2.Code inserts date and timestamp into the newly opened comment field.
3.If you select "Edit Comment", it will create a two 's after the last typed
word (paragraph). And then timestamp there.

could anyone lend a hand? (well, alittle more than a hand)

Hi Ben

Only possible with code as far as I know

See this page
http://www.contextures.com/xlcomments01.html



--
Regards Ron de Bruin
http://www.rondebruin.nl


"BenMetz" wrote in message ...
I am looking for a small piece of code that will format a comment feild in
microsoft excel to start with the current date and time.

this is details of what exactly the code needs to do:

1.Insert Comment
2.Code inserts date and timestamp into the newly opened comment field.
3.If you select "Edit Comment", it will create a two 's after the last typed
word (paragraph). And then timestamp there.

could anyone lend a hand? (well, alittle more than a hand)

helped me a few ways. show me how to add a comment, and how to remove the
username. however, i have no programming knowledge and wouldn't be able to
complete the script :) so buddy, wanna just write it all for me? :/ (sorry
for being lazy, don't have the time to start learning syntax.)

"Ron de Bruin" wrote:

Hi Ben

Only possible with code as far as I know

See this page
http://www.contextures.com/xlcomments01.html



--
Regards Ron de Bruin
http://www.rondebruin.nl


"BenMetz" wrote in message ...
I am looking for a small piece of code that will format a comment feild in
microsoft excel to start with the current date and time.

this is details of what exactly the code needs to do:

1.Insert Comment
2.Code inserts date and timestamp into the newly opened comment field.
3.If you select "Edit Comment", it will create a two 's after the last typed
word (paragraph). And then timestamp there.

could anyone lend a hand? (well, alittle more than a hand)

Hi Ben

You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment.

Sub Test()
Dim cmt As Comment
Set cmt = ActiveCell.Comment
If cmt Is Nothing Then
ActiveCell.AddComment
ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss")
Else
If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then
ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _
& Format(Now, "dd-mmm-yy h-mm-ss")
Else
ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss")
End If
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"BenMetz" wrote in message ...
helped me a few ways. show me how to add a comment, and how to remove the
username. however, i have no programming knowledge and wouldn't be able to
complete the script :) so buddy, wanna just write it all for me? :/ (sorry
for being lazy, don't have the time to start learning syntax.)

"Ron de Bruin" wrote:

Hi Ben

Only possible with code as far as I know

See this page
http://www.contextures.com/xlcomments01.html



--
Regards Ron de Bruin
http://www.rondebruin.nl


"BenMetz" wrote in message ...
I am looking for a small piece of code that will format a comment feild in
microsoft excel to start with the current date and time.

this is details of what exactly the code needs to do:

1.Insert Comment
2.Code inserts date and timestamp into the newly opened comment field.
3.If you select "Edit Comment", it will create a two 's after the last typed
word (paragraph). And then timestamp there.

could anyone lend a hand? (well, alittle more than a hand)

Or to add another date at the end of the existing comment, and open the
comment for editing:

'=========================
Sub CommentDateTimeAdd()

Dim strDate As String
Dim cmt As Comment
Dim lBreak As Long
Dim lArea As Long

strDate = "dd-mmm-yy hh:mm:ss"
Set cmt = ActiveCell.Comment

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.text text:=Format(Now, strDate) & Chr(10)
Else
cmt.text text:=cmt.text & Chr(10) _
& Format(Now, strDate) & Chr(10)
End If

With cmt.Shape.TextFrame
.Characters.Font.Bold = False
End With

SendKeys "%ie~"

End Sub
'=========================

Ron de Bruin wrote:
Hi Ben

You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment.

Sub Test()
Dim cmt As Comment
Set cmt = ActiveCell.Comment
If cmt Is Nothing Then
ActiveCell.AddComment
ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss")
Else
If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then
ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _
& Format(Now, "dd-mmm-yy h-mm-ss")
Else
ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss")
End If
End If
End Sub




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks Debra for this version

Add it to your site
I am sure others can use it to

Ron

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Debra Dalgleish" wrote in message ...
Or to add another date at the end of the existing comment, and open the comment for editing:

'=========================
Sub CommentDateTimeAdd()

Dim strDate As String
Dim cmt As Comment
Dim lBreak As Long
Dim lArea As Long

strDate = "dd-mmm-yy hh:mm:ss"
Set cmt = ActiveCell.Comment

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.text text:=Format(Now, strDate) & Chr(10)
Else
cmt.text text:=cmt.text & Chr(10) _
& Format(Now, strDate) & Chr(10)
End If

With cmt.Shape.TextFrame
.Characters.Font.Bold = False
End With

SendKeys "%ie~"

End Sub
'=========================

Ron de Bruin wrote:
Hi Ben

You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment.

Sub Test()
Dim cmt As Comment
Set cmt = ActiveCell.Comment
If cmt Is Nothing Then
ActiveCell.AddComment
ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss")
Else
If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then
ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _
& Format(Now, "dd-mmm-yy h-mm-ss")
Else
ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss")
End If
End If
End Sub




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

thanks alot for all the replies, so far everything has worked correctly.

just curious though, can i arrange ti so either the "Insert Comment" in the
context menu activates the macro? when i set a shortcut to the macro, it adds
the date/time, but it doesn't open the comment.

and are there any other time formats? like day fo the week?

"Debra Dalgleish" wrote:

Or to add another date at the end of the existing comment, and open the
comment for editing:

'=========================
Sub CommentDateTimeAdd()

Dim strDate As String
Dim cmt As Comment
Dim lBreak As Long
Dim lArea As Long

strDate = "dd-mmm-yy hh:mm:ss"
Set cmt = ActiveCell.Comment

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.text text:=Format(Now, strDate) & Chr(10)
Else
cmt.text text:=cmt.text & Chr(10) _
& Format(Now, strDate) & Chr(10)
End If

With cmt.Shape.TextFrame
.Characters.Font.Bold = False
End With

SendKeys "%ie~"

End Sub
'=========================

Ron de Bruin wrote:
Hi Ben

You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment.

Sub Test()
Dim cmt As Comment
Set cmt = ActiveCell.Comment
If cmt Is Nothing Then
ActiveCell.AddComment
ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss")
Else
If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then
ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _
& Format(Now, "dd-mmm-yy h-mm-ss")
Else
ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss")
End If
End If
End Sub




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html