If you add comments to worksheet, eventually those comments might wander away from their original locations. Sometimes the comments change size too, and you need to adjust them. Instead of making changes manually, move or resize Excel comments with macros, to save time.
Long ago, I shared macros to add comments, format or number them, change the author name, and other exciting things! You can see those comment macros on my Contextures site.
For example, here is my code to change all the comments, on all sheet in the active workbook, to Times New Roman, 12 pt font.
Sub FormatAllComments() 'www.contextures.com/xlcomments03.html Dim ws As Worksheet Dim cmt As Comment For Each ws In ActiveWorkbook.Worksheets For Each cmt In ws.Comments With cmt.Shape.TextFrame.Characters.Font .Name = "Times New Roman" .Size = 12 End With Next cmt Next ws End Sub
New Comment Macros
Now there’s a new page with comment macros, contributed by J. Woolley.
There is a sample workbook to download, and it has 6 sheets with buttons to run the macros, and cells with comments for testing.
The workbook contains 6 main comment macros, with a separate sheet for testing each macro. Here’s the list, and quick note on what each macro does.
- CommentAuthors – remove author name from start of comment
- FormatComments – change comment font and size
- ResizeComments – change comment size to fit its text
- MoveComments – position each comment near its cell
- CommentShadows – show or hide the comment’s drop shadow
- UpdateComments – runs all of the macros listed above
Choose Macro Options
Instead of having the options written into the macros, message boxes and input boxes let you control the macros before you run them.
First, each macro asks if you want to run the macro on the selected cells, or the entire worksheet.
NOTE: J. Woolley included a macro that changes the message box button text. This message has “Sheet”, “Selection” and “Quit” buttons, instead of “Yes”, “No” and “Cancel”.
Some macros have further confirmations, as you go along. For the CommentAuthors macro, you confirm for each author name, so you can skip the changes for some, and make changes for others.
In some macros, you can input the value(s) that you want to use, such as a font name or font size.
Resize Excel Comments with Macros
The most complex macro is the one to resize Excel comments. There are three “Resize Excel Comments” macros on my earlier page, using different methods to adjust the size. Some work better than others, but comment resizing is a tricky business!
I tested my “Resize by Cell Height” macro, to compare it with the results from J. Woolley’s resize macro. In some cases, the results were almost identical. My macro sets a width though, so it didn’t make comments narrow, where that was a good option. J. Woolley’s macro prompts you to enter a maximum width, but uses a narrower width where possible.
Here’s an example of his narrower result (max of 200), compared to my comment that used the set width of 200. I like the narrower comment better – it just fits the longest line of text.
Get the Sample Workbook
The zipped file is in xlsm format, so be sure to enable macros when you open the workbook, if you want to test the macros.