Has Excel ever moved or resized comments for you? Would you like to be able to reset to default position, and resize all existing comment boxes to fit the size of your comments?
Here is an Excel add In that allows you to select a cell, range of cells, or the used area of the worksheet, and will then tell you the number of comments found, and ask permission to relocate and resize the associated comment boxes.
To relocate the comment’s box to the default location is not technically difficult. Where this add in differs from others, is that it does not use a fudge factor in calculating the size of the box. This add in takes into account all of your comment’s font styles, line returns, special characters, etc., sets a configurable width, and then sizes the comment box just tall enough to display the comment.
Caveats:
- Tested on excel 2010 on windows 7. Might work with other versions
- Not tested with images attached to comments
- Macros must be enabled
Install the add in:
- Dounload Excel Add In – Fix Comments
- If you need to find your Excel Add Ins folder location:
- Open Excel, go to Excel Options, then Add Ins, then Manage -> Go
- Now in the Add Ins dialogue, click Browse and note your folder location.
- Place Fix.Comments.xlam in your Excel Add Ins folder.
- Reopen the Add Ins dialogue, check the Fix.Comments, choose OK to load the add in and close the dialogue
- Your ribbon should now display the “Fix” tab. Select a cell or cells that have comments, and experiment with the buttons in the Comments group.
Change Comment Box Width:
- After the add in is installed, open Excel, then open the visual basic editor (VBE)
- In the VBE project browser, expand “VBAProject (Fix.Comments.xlam),” then “Modules,” then double click the “Comments” module.
- Now in the code window, find the sub named CommentsResizeReposition, then find the variable declaration “bPreferredWidth = 100”
- Edit the “100” to the number of points you want this add in to use when resizing your comment boxes.
- Save your changes (Ctrl + S) to Fix.Comments.xlam.
- Close the VBE and proceed to use the add in.
Many thanks to my brother for his time taken to suggest improvements to this post.