Resize Excel Comments with Macros

Resize Excel Comments with Macros

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.

commentnumber01

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.

commentmacros07

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.

  1. CommentAuthors - remove author name from start of comment
  2. FormatComments - change comment font and size
  3. ResizeComments - change comment size to fit its text
  4. MoveComments - position each comment near its cell
  5. CommentShadows - show or hide the comment's drop shadow
  6. 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".

commentmacros04

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.

commentmacros09

Input Boxes

In some macros, you can input the value(s) that you want to use, such as a font name or font size.

commentmacros13

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.

commentresize06

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.

____________________________

2 thoughts on “Resize Excel Comments with Macros”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.