Comment with date stamp

[Keyrookie]

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

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

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

Thanks,

 

[Gord Dibben]

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

 

[Keyrookie]

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

Any ideas...

 

[Gord Dibben]

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 Insert>Comment if you wish.

Steps for Excel 2003 and earlier.

Stick the macro in a general module in your workbook.

Then in Excel Tools>Customize.

Insert>Comment..............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

 

[Keyrookie]

Thanks Gord,

I've followed your instructions and have copied the code into Module 4
I then went to Tools>Customize>Insert>Comment but I can't figure ou
how to assign the macro to the Comment.

I'm using Excel 2003. I may have missed something so if you will giv
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 afte
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]

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 Tools>Macro>Macros?

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

Tools>Customize

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

 

[Keyrookie]

Gord,

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

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

K

 

[Gord Dibben]

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

My instructions were

I will try again.

With Tools>Customize 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

 

[Keyrookie]

Thanks Gord,

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

Change the User Name
Instead of showing the user name at the start of a comment, you ca
change to something generic, such as "Note:" However, this chang
affects the User Name in all Microsoft Office programs, so you may wan
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 cod
I did not reset my User Name and that code has reset my User Name in al
my Office programs. Now I don't know how to reverse the process. Is i
even necessary? It seems to me it is only because I can't get the cod
you sent to work. All I was wanting to do was to have the code yo
sent to function in Excel only. The previous code (above) affects al
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 withou
this forum and without your help.

K

 

[Gord Dibben]

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

 

[Keyrookie]

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

I've used your code and simply assigned your macro to a button and i
works perfectly. I can't figure out how to get your code to work whe
"Insert Comment" is selected because of my previous actions. I'v
followed all your instructions and the code is sitting where it belong
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 "Insert>Module"
5. Paste your code into Module1
6. Return to sheet
7. Click "Tools>Customize" 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", windo
closes
12 Click "Close" on the still open "Customize" window

Am I missing anythng?