Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comment format | Excel Discussion (Misc queries) | |||
Default Comment Format | Excel Discussion (Misc queries) | |||
Decide comment format 'globally'? Restore format with ws_change? | Excel Discussion (Misc queries) | |||
Comment Connector format | Excel Discussion (Misc queries) | |||
How do I set up the default format for a comment? | Excel Discussion (Misc queries) |