One of our customers uses comments extensively in Excel and wanted to know how to resize all the comments on a particular worksheet. If you have tried doing this you will realise that you can’t multiple select comments, so you end up having to resize each comment individually. Now when you have 20 or 30 comments on a sheet this becomes very time consuming.
We wrote a VBA macro that allows the user to specify width and height values that will then be applied to all comments on a sheet. The macro also gives you the option to move comments back to their original position.
Here’s a screenshot of the dialog box that the macro gives you.
The macro is available here as a free add-in.
To install the add-in (for brevity these instructions assume you are a fairly competent Excel user)
- Download it and open it.
- Save the worksheet as an add-in – this is a Save as type Give it a file name that you will remember.
- You now need to go to your Excel options screen. In Excel 2007 click on the Office button and select Excel Options in the menu. In 2010 and 2013, click on the File tab and select Options.
- Select Add-ins within the Options screen and then in the Manage drop down (bottom of the screen) select Excel Add-ins and then click the Go… button
- In the Add-ins screen tick the box next to your new add-in – it will be labelled with the file name you gave it in step 2. Click on OK to confirm
- You now need a button to play your macro. Staying in the Excel Options screen click on the Quick Access Toolbar option down the left side of the screen. In the Choose commands from menu select Macros.
- From the list of macros add commentsctrl to the Quick Access Toolbar by clicking on Add
- Click Modify to change the button image and description. Click on OK to confirm. And OK again to exit the Excel Options screen. You can now play the macro from your Quick Access Toolbar.