Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...
Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Editing a Comment Close to Its Cell.
Peggy has several cells in a worksheet that have comments associated with them. When she right-clicks on one of these cells, she can choose Edit Comment from the resulting Context menu in order to edit the comment. If the comment was one she created in a previous session with Excel, it is not unusual for the comment to open up elsewhere in the worksheet, sometimes several screens away. Peggy is wondering if there is a way to make the comment appear next to the cell it goes with.
This condition can be caused by several things. For instance, it is possible that while previously editing the comment, you clicked the comment box's border and dragged the comment to a different place on the worksheet. If you did this, then Excel remembers where the comment was moved to and always displays it in the remembered location.
Another common cause is that you do some filtering on your data, which results in some of the rows or columns being hidden while the filter is in place. If you then edit comments in the filtered cells, you have effectively "moved" the comment from the original location to a new location that is associated with the row or column visible on the screen. When you later remove the filter and try to edit the comment, it remembers where it was previously edited, and that is where the new editing opportunity takes place.
In both of these instances, the normal solution is to just grin and bear it—manually move the cells from where they are to where you want them. However, if you have this problem with a lot of cells, all the manual moving can be a real bother. In that case, you may want to use a macro to do the moving for you.
Sub MoveComments() Dim cmt As Comment For Each cmt In ActiveSheet.Comments With cmt .Shape.Top = .Parent.Top .Shape.Left = .Parent.Offset(0, 1).Left End With Next End Sub
This macro moves all the comments in a worksheet so that their upper-left corner is the same as the upper-right corner of the cell to which they are attached. This puts the comments right next to their cells, which is where you want them.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3269) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Editing a Comment Close to Its Cell.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!