Use these comment macros to remove author name, change font or font size, move or resize comments, or add/remove comment shadows. Thanks to J. Woolley, who contributed these macros. Also, see more comment macros.
The Comment Macros workbook was contributed by J. Woolley. To see all the macros, and to test them, go to the download section below, and click the link.
There are several sheets in the workbook, and an overview of each main sheet and its macros, are in the following sections.
The Samples sheet has general notes about the workbook and its macros. The sheet is protected, with no password.
The Samples sheet also has 5 cells with sample comments.
Those comment cells can be copied to any of the other sheets, to resort the original comments, after testing the macros.
NOTE: When you paste the comments, they will move to their default position, near the top right corner of the comment cell. Go to Top
The workbook contains 6 comment macros, with a separate sheet for testing each macro. See the macro details in the sections below.
The comment macros are not listed on this page. To see the code, go to the Download section below, and click the link to download the workbook.
There are 2 code modules in the sample file:
Before running these comment macros:
Then, click the button on the worksheet, to run the macro.
After you click the button to run the macro, a confirmation message will appear.
The button on the CommentAuthors sheet runs a macro that removes an author name from the start of a comment. In the sample workbook, there are 3 comments with my name (Debra Dalgleish), one comment with Author at the start, and one comment with no name at the start.
After you click the CommentAuthors button to run the macro, the Sheet/Selection confirmation message appears, as described above.
Next, a message shows
NOTE: Excel can find the author name, even if it is not showing in the comment text.
Then, for each author, a confirmation message appears, asking if you want to remove that author's name from the beginning of the comment text.
The message shows
NOTE: If there are lots of comments to change, the macro's progress will show in Excel's status bar, at the bottom left of the Excel window
When the changes are completed, another message appears, with the revised count of comments that begin with the author's name.
Here is a screen shot of the revised comments.
The button on the FormatComments sheet runs a macro that changes the comment font and size. The macro does NOT change other format settings, such as bold, italic or color.
After you click the FormatComments button to run the macro, the Sheet/Selection confirmation message appears, as described above.
Next, a message with an input box asks you to enter a valid font name, with Tahoma as the default font.
If you click OK, the next message appears, where you can enter a font size -- 9 appears as the default.
If you click OK, a confirmation message appears, with a summary of the changes. Click OK or Cancel
When the changes are completed, another message appears, with the count of updated comments, and the revised comments have the selected font and font size.
The button on the ResizeComments sheet runs a macro that changes the comment size to fit its text. The macro does NOT change any font settings, such as font type, size, bold, italic or color.
After you click the ResizeComments button to run the macro, the Sheet/Selection confirmation message appears, as described above.
Next, a message with an input box asks you to enter a number for the maximum width (in points), with 180 as the default width. Excel assumes 72 points per inch, so if you want the comments to be 3 inches wide, enter 216 as the width (72 points x 3 inches)
NOTE: Each comment will be set to its "best width", based on its text, and will not exceed that maximum width.
If you click OK, a confirmation message appears. Click OK if you want to go ahead with the resizing.
NOTE: While the macro is running, you can press the Esc key, to stop the macro.
A final message will appear when the resizing is completed.
Here are comments before the resizing.
The next screen shot shows those comments after resizing -- you might want to rearrange them, to show their text.
The button on the MoveComments sheet runs a macro that positions each comment near its cell.
After you click the MoveComments button to run the macro, the Sheet/Selection confirmation message appears, as described above.
Next, confirmation message appears. Click OK if you want to go ahead with the moves.
A final message will appear when the moving is completed. Each comment that was moved will be near the top right corner of its cell.
The button on the CommentShadows sheet runs a macro that activates or deactivates the drop shadow on each comment.
A confirmation message shows which option you clicked, and how many comments will be affected.
A final message will appear when the shadows have been completed.
In this screen shot, shadows were activated for all the comments.
The button on the UpdateComments sheet runs a macro that offers to run all of the Comment Macros, for ALL worksheets in the active workbook.
NOTE: Before you run the UpdateComments macro, you can select specific cells on each sheet, if you'd like to make changes to those comments only.
In the sample workbook, the macro could not make changes on the first 2 sheets, and is asking if changes should be made on the 3rd sheet - CommentAuthors.
Click Yes to change that sheet, or click No to go on to the next sheet.
For each sheet where comments can be changed, the UpdateComments macro runs each of the other macros, and shows the same message boxes that are described in the previous sections.
A final message will appear when the UpdateComments macro is completed.
In the sample file, the last two sheets do not have comments, so no changes were made on those sheets. Comments were updated on 1 sheet.
Last updated: December 21, 2020 3:51 PM