Edit default properties for "insert comment" in Excel 2007?

[seme22]

I am using Windows XP Pro and Office 2007. I am working on a spreadsheet in Excel and using the "insert comment" feature. Each time I create a comment box for a given cell, it is automatically populated with my user name. No biggie I can delete it, then I adjust all the parameters for the comment box (center justify, font & size etc...) The trouble is that I have like 500 more of these to do and adjusting all these parameters can start to get tedious.

Is there a way to edit the default parameters for all comment boxes created so the box is all ready to go with the correct font, font size, etc... as soon as I open it?

Just in case there are different types of comment boxes I may be unaware of.... I create mine by right clicking the cell I want a comment attached to and then select "Insert Comment".

Thanks for any help.

[calvert]

Here is a method to do as you wish, using a macro and an event handler. This will allow you to select a cell, activate a keyboard shortcut, and create a new comment. The new comment will not include your username and will autosize to the text entered for the comment, once you exit the cell.

This example uses Arial, 12 pt, regular font. Change the parameters as desired.

Copy the following macro to the clipboard:

Sub Add_or_Modify_Comment()
Dim cCom As Comment
Set cCom = ActiveCell.Comment
If cCom Is Nothing Then
Set cCom = ActiveCell.AddComment
cCom.Text Text:=""
End If
cCom.Visible = True
cCom.Shape.Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
End With
With Selection.ShapeRange.Fill
.Visible = msoTrue
.Solid
.ForeColor.RGB = RGB(255, 255, 204)
End With
End Sub


Press ALT + F11

In the menus at the top of the VBE, select INSERT > MODULE

Paste the macro into the editing area to the right.

Still in the VBE, copy the following event handler to the clipboard:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End Sub

Double click 'This Workbook' in the Microsoft Excel Objects in the upper left quadrant.

Paste the event handler into the editing area to the right.

Close the VBE and return to the worksheet.

Press ALT + F8

When the Macros window opens, highlight the comment macro and click 'Options...'.

Enter a letter to be used as a keyboard shortcut. Use a letter that will not override the common built-in shortcuts. For example, don't use 'c', 'v', etc. as you would not then be able to 'copy' or 'paste' using the standard keyboard shortcuts.

Click 'OK'.

To enter a comment in any worksheet in the workbook, press CTRL + your shortcut letter.

The add comment dialog will initiate. Simply begin keying your comment. You do not have to click inside the comment box.

Any difficulties, please advise by email.