Post a date/time stamp on your comments automatically

by Sean Kavanagh

Application:

Microsoft Excel 2000/2002/2003/2004/2007

Operating Systems:

Microsoft Windows, Macintosh

 

Excel’s comments include the user’s name but nothing else — and there’s no easy way to change this default format. Our VBA solutions allow you to modify the way your new comments appear, so you can add helpful information such as the current date.

 

To enhance all your comments automatically, we’ll:

•     Examine a typical default comment and what it includes.

•     Use VBA to add a date/time stamp to all new comments.

•     Suggest other ways to modify your new comment style through additional VBA procedures.

 

Unfortunately, Excel doesn’t let you decide how to display comments. This limitation can make it difficult to track several users’ comments. For example, you have two conflicting comments from one reviewer. Which comment was added most recently? We’ll show you how to create a VBA procedure that automates customizing the cell comment to meet your needs, whether you just want to add a date/time stamp or enhance the format.

 

Examine manually created comments

When you create a cell comment using the Insert | Comment menu command, Excel displays a comment box resembling the one shown in Figure A. The new comment automatically includes your name at the top and the insertion point is placed on a new line, ready for you to add the appropriate text.

When you programmatically create a new comment, you can simultaneously specify comment text. Let’s look at a few ways to customize new comments.

 

A:

Excel doesn’t give you a way to modify the default comment style.

 

Add a date/time stamp

First, let’s change the default comment style so it includes only the current date and time.

 

Download: You can copy and paste the VBA procedures we create by downloading and extracting the text file code.txt from the URL given at the beginning of this article.

 

 

To create a comment style with only a date/time stamp:

1.       Launch Excel and open a new workbook.

2.      Choose Tools | Macro | Visual Basic Editor from the menu bar — or just press [Alt][F11] — to open the Visual Basic Editor (VBE).

3.      Choose Insert | Module from the VBE’s menu bar. At the insertion point, add the code shown here:

 

Sub DatedComment()

On Error Resume Next

ActiveCell.AddComment Str(Now) & vbLf

End Sub

 

When adding the comment, our procedure simply sets the new comment’s Text property equal to the current date and time. We used the Str() function because the Text property requires a string value — otherwise you’d receive an error. Also, we appended a line feed character to move the insertion point one row below the date, so that when you edit the comment, your insertio