When you insert comments to cells, the size of the comment box is the default. If you enter a lot of contents into it, some of the content will not be visible directly. Is it possible to fit the size of the comment box to its content automatically?
Download Sample File with Macros here: AutofitComments.xls
Auto-size comment box to fit its content in a worksheet
In Excel, you can drag the comment box to your need size manually one by one, but if there are numerous comment boxes need to be resized, this way will be tedious and time-consuming. In this case, you can apply the following VBA code.
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following macro in the Module window.
VBA code: Auto-size comment boxes in a worksheet
|
3. Then press F5 key to run this code, and all of the comment boxes in the active worksheet have been resized to fit their contents as following screenshot shown:
Tip: This code only work in the current worksheet, and it cannot automatically resize the new comment boxes you add.
Auto-size comment box to fit its content in a range of cells
If you just need to auto-size a range of cell comment boxes, you can use the following VBA code.
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following macro in the Module window.
VBA code: Auto-size comment boxes in a range of cells
|
3. Then press F5 key to execute this code, and a prompt box will appear to remind you selecting a range of cells that you want to resize the comment boxes, see screenshot:
4. And then click OK, your selected cell comment boxes have been auto-fitted to the comment contents.
Tip: This code cannot automatically resize the new comment boxes you add.
Courtesy: extendoffice.com
Nice trick. If you add to workbook under SheetActivate; runs auto