![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsMK-MrqIHSJCJYv04g_2lsmoUsQb6vvrt8lKUM2pofWpAWyNkPZdMoxhp1VWLqtVTzYWHzZgBPuHhhH6Aie0hCYA3rxNUruzmEX8Cio2XXgcoyctyHX7UxuL9-i4Hlb_0wJsbTcJwGno/s320/worsheetmacro.jpg)
Problem: How to adjust size for all comments in excel at once
Here is the solution;
http://www.codeforexcelandoutlook.com/excel-vba/comment-shape-formatting/
New macro:
Tools - Macro - Macros
choose name
create
delete
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:
Nice and thanks!
Post a Comment