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

Gord,

I've followed every instruction exactly and everything shows up properly but I hit a wall at the Comment.

When I select Comment I can only right click and that gives me a little plus sign and a small button. I've tried numerous times but I can't left click. Again, I'm using Excel 2003. What am I missing?

K

I think you are trying to select from Insert in Commands in the customize
dialog.

My instructions were

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

I will try again.

With ToolsCustomize dialog open.............

Up on WORKSHEET menu bar left click on Insert.

This should unfold the Insert Menu.

On that menu you will see the choice "Comment".

RIGHT-click on that item and another menu should unfold.

On that menu will be several items about button images etc.

Bottom item will be "Assign Macro"


Gord

On Thu, 24 Dec 2009 17:40:12 +0000, Keyrookie
wrote:


Gord,

I've followed every instruction exactly and everything shows up
properly but I hit a wall at the Comment.

When I select Comment I can only right click and that gives me a little
plus sign and a small button. I've tried numerous times but I can't
left click. Again, I'm using Excel 2003. What am I missing?

K

Gord Dibben;912397 Wrote:
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-------

Thanks Gord,

I did misunderstand and made the correction. Unfortunately I have another issue. I had used this macro from the Contextures site and now your code will not work:

Change the User Name
Instead of showing the user name at the start of a comment, you can change to something generic, such as "Note:" However, this change affects the User Name in all Microsoft Office programs, so you may want to reset the name before you exit Excel.

To set a generic label:
Sub CommentNote()
Application.UserName = "Note"
End Sub

To reset the User Name:
Sub CommentName()
Application.UserName= "John Smith"
End Sub


Whenever I select "Insert Comment" I still get the just the "Note" showing as described in the above code. When I followed the above code I did not reset my User Name and that code has reset my User Name in all my Office programs. Now I don't know how to reverse the process. Is it even necessary? It seems to me it is only because I can't get the code you sent to work. All I was wanting to do was to have the code you sent to function in Excel only. The previous code (above) affects all Office prgs. If I can resolve this last issue it will make my day!

Thanks for all your input and time. I don't know what I'd do without this forum and without your help.

K

The code I posted in response to your request for non-military time does not
care what you have for a username. It does not apply the username to the
Comment.

Have you assigned this macro to the InsertComment command?

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

As far as resetting your usename, have you tried the reset macro you just
posted?

To reset the User Name:
Sub CommentName()
Application.UserName= "John Smith" 'your name here
End Sub


Gord

On Fri, 25 Dec 2009 15:47:49 +0000, Keyrookie
wrote:


Thanks Gord,

I did misunderstand and made the correction. Unfortunately I have
another issue. I had used this macro from the Contextures site and now
your code will not work:

Change the User Name
Instead of showing the user name at the start of a comment, you can
change to something generic, such as "Note:" However, this change
affects the User Name in all Microsoft Office programs, so you may want
to reset the name before you exit Excel.

To set a generic label:
Sub CommentNote()
Application.UserName = "Note"
End Sub

To reset the User Name:
Sub CommentName()
Application.UserName= "John Smith"
End Sub


Whenever I select "Insert Comment" I still get the just the "Note"
showing as described in the above code. When I followed the above code
I did not reset my User Name and that code has reset my User Name in all
my Office programs. Now I don't know how to reverse the process. Is it
even necessary? It seems to me it is only because I can't get the code
you sent to work. All I was wanting to do was to have the code you
sent to function in Excel only. The previous code (above) affects all
Office prgs. If I can resolve this last issue it will make my day!

Thanks for all your input and time. I don't know what I'd do without
this forum and without your help.

K



Gord Dibben;912600 Wrote:
I think you are trying to select from Insert in Commands in the
customize
dialog.

My instructions were
--
On menubar select Insert which will drop down to reveal sub menu.--

I will try again.

With ToolsCustomize dialog open.............

Up on WORKSHEET menu bar left click on Insert.

This should unfold the Insert Menu.

On that menu you will see the choice "Comment".

RIGHT-click on that item and another menu should unfold.

On that menu will be several items about button images etc.

Bottom item will be "Assign Macro"


Gord

On Thu, 24 Dec 2009 17:40:12 +0000, Keyrookie
wrote:
-

Gord,

I've followed every instruction exactly and everything shows up
properly but I hit a wall at the Comment.

When I select Comment I can only right click and that gives me a
little
plus sign and a small button. I've tried numerous times but I can't
left click. Again, I'm using Excel 2003. What am I missing?

K

Gord Dibben;912397 Wrote: -
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----------

Yes, to both questions. That's why I'm stumped! I've tried resetting the user name & reversing my earlier steps. I do remember an instruction saying that when you run the Macro (the ealier one that changed the User name to "Note") that it will change that code for all Office prgs. I agree that your code should operate and not be affected by User Name modifications, but somehow it is.

I've used your code and simply assigned your macro to a button and it works perfectly. I can't figure out how to get your code to work when "Insert Comment" is selected because of my previous actions. I've followed all your instructions and the code is sitting where it belongs and assigned to the Comment, just like you said. What am I missing?

Here's a step-by-step:

1. Copy your code
2. Open a new workbook
3. Click on "View Code" from any sheet
4. On the Visual Basic window click "InsertModule"
5. Paste your code into Module1
6. Return to sheet
7. Click "ToolsCustomize" and a window pops up
8. Select "Insert" menu at top of page (not in the open window)
9. Select "Comment" from drop-down menu from "Insert"
10 Right click and select "Assign Macro" from drop-down menu
11 Click on "CommentDateTimeAdd" from Macro window"OK", window closes
12 Click "Close" on the still open "Customize" window

Am I missing anythng?