February 23, 2011

Excel comment changed size

Problem: How to adjust size for all comments in excel at once

Here is the solution;

New macro:
Tools - Macro - Macros
choose name
Sub s()

End Sub

copy paste this macro and run:

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

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

for larger comments numbers can change here:
Call CommentSize(wksht, 150, 150)

The change in comment size was caused by user intervention - automatic filter, insert cells, changing size of cells etc.

Size of one comment can be made automatic but only for that selected comment, one at the time.
Right click comment - Format comment
Alignment - check Automatic size

1 comment:

Anonymous said...

Nice and thanks!