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.
Comment Macros
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”.
Please Confirm
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.
Input Boxes
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
To see J. Woolley’s macros, go to the Excel Comment Macros page, and go to the Download section. Because of Microsoft security for downloaded files, you might need to unblock the zipped file.
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.
____________________________
These Post It comments can sure be aggravating, like when they silently get in the way of being able to delete columns. FYI Microsoft is finally bringing Excel comments into the 21st century with threaded comments:
https://www.microsoft.com/en-us/microsoft-365/blog/2018/07/31/new-to-microsoft-365-in-july-integrating-apps-and-empowering-businesses/
It looks like this is for Microsoft 365, but not necessarily for Office 365.
The latest version of my Comment Macros can be downloaded as a VBA module BAS file from Google Drive:
https://drive.google.com/file/d/1BDGDjcvzhFjK-7fXr5h1DQDR9mdAkNOZ