How can I add comment and keep the format for all new workbooks
 

'Put the following code in the ThisWorkbook module...
'--------------------
Option Explicit
Public WithEvents objCtrl As Office.CommandBarButton
Public WithEvents objCtrlRC As Office.CommandBarButton

Private Sub objCtrl_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
Application.OnTime Now, "FormatComments"
End Sub

Private Sub objCtrlRC_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
Application.OnTime Now, "FormatComments"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set objCtrl = Nothing
Set objCtrlRC = Nothing
End Sub

Private Sub Workbook_Open()
Set objCtrl = Application.CommandBars.FindControl(ID:=1589)
Set objCtrlRC = Application.CommandBars("Cell").Controls("Insert Co&mment")
End Sub
'------------

'Put this code in a standard module...
Option Explicit
Sub FormatComments()
With ActiveCell.Comment
.Text Format(Date, "mmmm dd, yyyy")
.Shape.Height = 120
.Shape.Width = 120
With .Shape.TextFrame.Characters.Font
.Name = "Times New Roman"
.Size = 12
.Bold = True
.ColorIndex = xlColorIndexAutomatic
End With
End With
End Sub
'--------------

The workbook_open sub must be run first to activate everything.
Then just click the "Comment" button on the Insert menu or
the "Insert Comment" button on the cell right-click menu.
The code will not work in XL97.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Newbie"
wrote in message
I want to be able to add comment to any workbook, new or old and keep the
same formatted text. The original size is 8 pt. and I would like it to be 12
with the box size of 120 x 120 and insert the date
At the moment it only works with the sheet in which I assign the macro, but
not any new work books.

Sub CommentFormat()
'adds TimesNewRoman comment or positions
'cursor at end of existing comment text
Dim cmt As Comment
Set cmt = ActiveCell.Comment
If cmt Is Nothing Then
ActiveCell.AddComment Text:=""
Set cmt = ActiveCell.Comment
With cmt.Shape.TextFrame.Characters.Font
.Name = "Times New Roman"
.Size = 12
.Bold = True
.ColorIndex = 0
End With
cmt.Text Text:=Format(Now, strDate) & Chr(10)
cmt.Shape.Height = 120
cmt.Shape.Width = 120
End If
SendKeys "%ie~"
End Sub

Any help is greatly appreciated...