Adjusting comment properties can be frustrating, especially if you're stuck doing it manually. Here is some sample VBA code that lets you adjust all comments at once, to keep your workbook looking consistent. See All about Comment shapes for even more sample VBA code.
To follow along manually, first display a comment by right clicking on any cell with a comment and choosing Show/Hide Comments.
Then, right-click on the comment's border and choose Format Comment.
Adjust comment font
To adjust the font for a comment, we'll use a custom Enum section. Place the following code at the top of a standard module.
Public Enum fontStyles xlRegular xlItalic xlBold xlBoldAndItalic End Enum
To adjust the comments, we'll use a function that takes as its arguments: a Worksheet Object, the name of the font you want (the default being Verdana, but you can change this to any font you want), the font style (defined by enumerated constants) and the font size (default 10).
Function CommentFont(wksht As Excel.Worksheet, _
Optional fontName As String = "Verdana", _
Optional fontStyle As fontStyles = xlRegular, _
Optional fontSize As Long = 10)
Dim cmt As Excel.Comment
Dim isItalic As Boolean
Dim isBold As Boolean
Select Case fontStyle
Case 0 ' xlRegular
isItalic = False
isBold = False
Case 1 ' xlItalic
isItalic = True
isBold = False
Case 2 ' xlBold
isItalic = False
isBold = True
Case 3 ' xlBoldAndItalic
isItalic = True
isBold = True
End Select
For Each cmt In wksht.Comments
With cmt.Shape.TextFrame.Characters.Font
.Name = fontName
.Italic = isItalic
.Bold = isBold
.Size = fontSize
End With
Next cmt
End Function
The reason we pass a Worksheet object is so that we can loop through all the worksheets in a workbook and provide a consistent look for the comments found there. The following procedure will loop through all the worksheets in the active workbook and set the font to 10pt Tahoma Italic.
Sub TestCmtFont()
Dim wksht As Excel.Worksheet
Dim wb As Excel.Workbook
Set wb = ActiveWorkbook
For Each wksht In wb.Worksheets
Call CommentFont(wksht, "Tahoma", xlItalic)
Next wksht
End Sub
Change comment alignment
The alignment for a comment determines how the text is positioned inside the comment box. As above, we pass a Worksheet Object to the function, so we can loop through the worksheets in a workbook. The dialog box allows us to auto-size the comment box, as well as set the horizontal and vertical alignment of text within the comment. The defaults are Top Left alignment, with no auto-sizing.
Function CommentAlignment(wksht As Excel.Worksheet, _
Optional AutoSize As Boolean = False, _
Optional horizAlign As XlHAlign = xlHAlignLeft, _
Optional vertAlign As XlVAlign = xlVAlignTop)
Dim cmt As Excel.Comment
For Each cmt In wksht.Comments
cmt.Shape.TextFrame.HorizontalAlignment = horizAlign
cmt.Shape.TextFrame.VerticalAlignment = vertAlign
cmt.Shape.TextFrame.autoSize = autoSize
Next cmt
End Function
The following sample code, then, will loop through all worksheets in a workbook and center the text within each comment.
Sub TestCmtAlignment()
Dim wksht As Excel.Worksheet
Dim wb As Excel.Workbook
Set wb = ActiveWorkbook
For Each wksht In wb.Worksheets
Call CommentAlignment(wksht, False, xlHAlignCenter, xlVAlignCenter)
Next wksht
End Sub
Change comment fill colors
There are a few options here, but I chose to focus on the fill color and transparency. The defaults are 1 and 0, respectively, which is white fill with no transparency. Transparency must be a number between 0 and 1, with 0 representing 0% and 1 representing 100%. So if you wanted 50% transparency, pass 0.5 for this parameter.
Function CommentColors(wksht As Excel.Worksheet, _
Optional fillColor As Long = 1, _
Optional transparent As Double = 0#)
Dim cmt As Excel.Comment
For Each cmt In wksht.Comments
cmt.Shape.Fill.ForeColor.SchemeColor = fillColor
cmt.Shape.Fill.Transparency = transparent
Next cmt
End Function
The following procedure will "reset" all comments to white fill color with 0% transparency.
Sub TestCmtColors()
Dim wksht As Excel.Worksheet
Dim wb As Excel.Workbook
Set wb = ActiveWorkbook
For Each wksht In wb.Worksheets
Call CommentColors(wksht)
Next wksht
End Sub
Adjust comment size
Setting the size programmatically is very simple. In addition to a Worksheet Object, you must set the Height and Width properties (Single).
Function CommentSize(wksht As Excel.Worksheet, hgt As Single, _
wdth As Single)
Dim cmt As Excel.Comment
For Each cmt In wksht.Comments
cmt.Shape.Height = hgt
cmt.Shape.Width = wdth
Next cmt
End Function
The following procedure sets each comment in the active workbook to about one inch tall and about one and a half inches wide.
Sub TestCmtSize()
Dim wksht As Excel.Worksheet
Dim wb As Excel.Workbook
Set wb = ActiveWorkbook
For Each wksht In wb.Worksheets
Call CommentSize(wksht, 50, 150)
Next wksht
End Sub
Comment Protection
Comments can be locked by setting the Locked (Boolean) property. This function assumes that you don't want the comments locked, so the function may be called with just the Worksheet Object as a parameter (see below).
Function CommentProtection(wksht As Excel.Worksheet, _
Optional lockComments As Boolean = False)
Dim cmt As Excel.Comment
For Each cmt In wksht.Comments
cmt.Shape.Locked = lockComments
Next cmt
End Function
Sub TestCmtProtection()
Dim wksht As Excel.Worksheet
Dim wb As Excel.Workbook
Set wb = ActiveWorkbook
For Each wksht In wb.Worksheets
Call CommentProtection(wksht)
Next wksht
End Sub
Adjust comment placement
Comments that move on their own really irritate me, so this code is a headache saver. The placement parameter is declared As XlPlacement so we can use Intellisense to select only from the enumerated constants used by the Placement property.
Function CommentPlacement(wksht As Excel.Worksheet, pos As XlPlacement)
' pos can be one of the following:
'xlFreeFloating = Don't move or size with cells
'xlMove = Move but don't size with cells
'xlMoveAndSize = Move and size with cells
Dim cmt As Excel.Comment
For Each cmt In wksht.Comments
cmt.Shape.Placement = pos
Next cmt
End Function
We can loop through each worksheet in the active workbook and set up comment placement…
Sub LoopThruWorksheets() Dim wb As Excel.Workbook Dim wksht As Excel.Worksheet Set wb = ActiveWorkbook ' loop through each worksheet For Each wksht In wb.Worksheets Call CommentPlacement(wksht, xlMove) Next wksht End Sub
…or loop through each open workbook and each worksheet in each workbook. In fact we can do this for each of the sample procedures above.
Sub LoopThruWorkbooks()
Dim wbs As Excel.Workbooks
Dim wb As Excel.Workbook
Dim wksht As Excel.Worksheet
Set wbs = Excel.Workbooks
' loop through all open workbooks
For Each wb In wbs
For Each wksht In wb.Worksheets
Call CommentPlacement(wksht, xlMove)
Next wksht
Next wb
End Sub
