Is there a way to have a comment, when "Insert Comment" is selected, to look like this?

12-19-09 (current date), 12:02 p.m. (current time)
NOTE:

In other words, have comments display current date & time when selected?

Thanks,
K

See Debra Dalgleish's site for full Insert Comment with date/time code

http://www.contextures.on.ca/xlcomments03.html#DateTime

Here is revised version which pre-supposes no existing Comment in cell and
adds the "NOTE:" all in bold font.

Sub CommentDateTimeAdd()
Dim strDate As String
Dim cmt As Comment

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

Set cmt = ActiveCell.AddComment
cmt.Text Text:=Format(Now, strDate) & Chr(10) _
& "NOTE:"

End Sub


Gord Dibben MS Excel MVP


On Mon, 21 Dec 2009 18:09:31 +0000, Keyrookie
wrote:


Is there a way to have a comment, when "Insert Comment" is selected, to
look like this?

12-19-09 (current date), 12:02 p.m. (current time)
NOTE:

In other words, have comments display current date & time when
selected?

Thanks,
K

Thank you, Gord. After following Debra's instructions I realize I'm wanting some further edits. Is there a way to have this without having to run a macro? Insert into the "ThisWorkbook" and have the time format not military?

Any ideas...

Sub CommentDateTimeAdd()
Dim strDate As String
Dim cmt As Comment

strDate = "dd-mmm-yy h:mm:ss AM/PM"
Set cmt = ActiveCell.Comment

Set cmt = ActiveCell.AddComment
cmt.Text Text:=Format(Now, strDate) & Chr(10) _
& "NOTE:"

End Sub

You can assign the macro to InsertComment if you wish.

Steps for Excel 2003 and earlier.

Stick the macro in a general module in your workbook.

Then in Excel ToolsCustomize.

InsertComment..............assign the macro to Comment command.

I have not worked with 2007 enough to give help on assigning a macro to an
existing command.

Ron de Bruin is the master of that.

http://www.rondebruin.nl/tips.htm

Excel 2007 Ribbon and QAT pages


Gord

On Tue, 22 Dec 2009 03:23:01 +0000, Keyrookie
wrote:


Thank you, Gord. After following Debra's instructions I realize I'm
wanting some further edits. Is there a way to have this without having
to run a macro? Insert into the "ThisWorkbook" and have the time format
not military?

Any ideas...

Gord Dibben;911705 Wrote:
See Debra Dalgleish's site for full Insert Comment with date/time code

http://www.contextures.on.ca/xlcomments03.html#DateTime

Here is revised version which pre-supposes no existing Comment in cell
and
adds the "NOTE:" all in bold font.

Sub CommentDateTimeAdd()
Dim strDate As String
Dim cmt As Comment

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

Set cmt = ActiveCell.AddComment
cmt.Text Text:=Format(Now, strDate) & Chr(10) _
& "NOTE:"

End Sub


Gord Dibben MS Excel MVP


On Mon, 21 Dec 2009 18:09:31 +0000, Keyrookie
wrote:
-

Is there a way to have a comment, when "Insert Comment" is selected,
to
look like this?

12-19-09 (current date), 12:02 p.m. (current time)
NOTE:

In other words, have comments display current date & time when
selected?

Thanks,
K-

Thanks Gord,

I've followed your instructions and have copied the code into Module 4. I then went to ToolsCustomizeInsertComment but I can't figure out how to assign the macro to the Comment.

I'm using Excel 2003. I may have missed something so if you will give me a step-by-step I would greatly appreciate it.

Here's what I did:

I recorded a new Macro (2) and copied your code into that macro.
I then located Macro2 in Module4 in VBA.
I followed the path above to get to the Comment command but after trying clicking and dragging I can't get any results.

Did I miss something along the way?

Thanks again for your time, you've already been very helpful.

K

I don;t know what you have done "copying the macro into Macro 2"

Is the macro as I posted in Module 4 as a standalone Sub?

Does it show up in ToolsMacroMacros?

If so..................

ToolsCustomize

On menubar select Insert which will drop down to reveal sub menu.

Do not drag anything.

Right-click on Comment and "assign macro.

Dialog with macro choices will pop up. Select CommentDateTimeAdd and OK


Gord

On Tue, 22 Dec 2009 23:44:41 +0000, Keyrookie
wrote:


Thanks Gord,

I've followed your instructions and have copied the code into Module 4.
I then went to ToolsCustomizeInsertComment but I can't figure out
how to assign the macro to the Comment.

I'm using Excel 2003. I may have missed something so if you will give
me a step-by-step I would greatly appreciate it.

Here's what I did:

I recorded a new Macro (2) and copied your code into that macro.
I then located Macro2 in Module4 in VBA.
I followed the path above to get to the Comment command but after
trying clicking and dragging I can't get any results.

Did I miss something along the way?

Thanks again for your time, you've already been very helpful.

K




Gord Dibben;911941 Wrote:
Sub CommentDateTimeAdd()
Dim strDate As String
Dim cmt As Comment

strDate = "dd-mmm-yy h:mm:ss AM/PM"
Set cmt = ActiveCell.Comment

Set cmt = ActiveCell.AddComment
cmt.Text Text:=Format(Now, strDate) & Chr(10) _
& "NOTE:"

End Sub

You can assign the macro to InsertComment if you wish.

Steps for Excel 2003 and earlier.

Stick the macro in a general module in your workbook.

Then in Excel ToolsCustomize.

InsertComment..............assign the macro to Comment command.

I have not worked with 2007 enough to give help on assigning a macro to
an
existing command.

Ron de Bruin is the master of that.

http://www.rondebruin.nl/tips.htm

Excel 2007 Ribbon and QAT pages


Gord

On Tue, 22 Dec 2009 03:23:01 +0000, Keyrookie
wrote:
-

Thank you, Gord. After following Debra's instructions I realize I'm
wanting some further edits. Is there a way to have this without
having
to run a macro? Insert into the "ThisWorkbook" and have the time
format
not military?

Any ideas...

Gord Dibben;911705 Wrote: -
See Debra Dalgleish's site for full Insert Comment with date/time
code

http://www.contextures.on.ca/xlcomments03.html#DateTime

Here is revised version which pre-supposes no existing Comment in
cell
and
adds the "NOTE:" all in bold font.

Sub CommentDateTimeAdd()
Dim strDate As String
Dim cmt As Comment

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

Set cmt = ActiveCell.AddComment
cmt.Text Text:=Format(Now, strDate) & Chr(10) _
& "NOTE:"

End Sub


Gord Dibben MS Excel MVP


On Mon, 21 Dec 2009 18:09:31 +0000, Keyrookie
wrote:
--

Is there a way to have a comment, when "Insert Comment" is
selected,-
to-
look like this?

12-19-09 (current date), 12:02 p.m. (current time)
NOTE:

In other words, have comments display current date & time when
selected?

Thanks,
K----