Gary''s Student has given you a link for a macro that you can use to change
all your existing Comments to the size you want (see "Format All Comments"
at the link). However, this doesn't address your other question of how to
change newly inserted Comments (obviously, without having to remember to run
the macro each time). There is a minor problem with being able to do this
since inserting a Comment does not trigger any events, so the VBA world has
no way of knowing it happened. I can give you code that *almost* does what
you want... it will not change the Comment's size when it is inserted unless
you change the active cell's location or select another sheet. That means if
you insert a Comment and click back into that same cell, the Comment's size
will not have changed... however, as soon as you click into another cell or
select another sheet, the Comment you just inserted will have its text size
changed.
Here is the event code that does this. To install it, right click the Excel
icon immediately to the left of the File menu item and select View Code.
Doing this will bring up the ThisWorkbook code window... simply copy/paste
the following into that window...
'************ START OF CODE ************
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
HandleCommentSizing
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
HandleCommentSizing
End Sub
Sub HandleCommentSizing()
Static PrevCellAddress As String
Static PrevCellSheetName As String
If Len(PrevCellAddress) > 0 Then
With Worksheets(PrevCellSheetName).Range(PrevCellAddress)
If Not .Comment Is Nothing Then
If .Comment.Shape.TextFrame.Characters.Font.Size <> 14 Then
.Comment.Shape.TextFrame.Characters.Font.Size = 14
End If
End If
End With
End If
PrevCellAddress = ActiveCell.Address
PrevCellSheetName = ActiveCell.Parent.Name
End Sub
'************ END OF CODE ************
