Comment display problem. Comments appear as arrow bars, instead of boxes with text. A pain to edit. Bug in Excel 2007 not addressed?

Question

Hi, I work for a small shipping company and we use an Excel spreadsheet to display pricing information. I maintain it and sometimes insert comments to be a little more specific.

Well, the problem I'm having is after inserting a comment one day in a cell. I'll open up the spreadsheet another day and the cell comment will not display as the box of text it once was. Instead, if I mouse over the cell with my cursor, the comment pops up as a flat arrow bar and not the box of text it once before. See below.

http://i20.photobucket.com/albums/b230/gizmomaguii/excel\_comment\_problem\_1.jpg

And when I try to edit one of these faulty comments, it appears as an expandable line way up at the top of the spreadsheet, miles above where the cell is actually located. See below.

http://i20.photobucket.com/albums/b230/gizmomaguii/excel\_comment\_problem\_2.jpg

I then have to scroll up and down the spreadsheet to find and fix these things and fix some of the same cells I thought I had fixed before because it's a constant problem. It all has become a very big pain.

Anyone have an idea of why my comments are displayed as arrow bars instead of comment boxes? And how I can get them to stay as comment boxes? Thanks.

Answer 1

The comments have moved, possibly as a result of hiding rows or columns or deleting cells.

I have experienced the same from time to time.

I would run a macro to re-position all the comments; something like this:

Sub RePositionComments()

  Dim WS As Worksheet

  Dim C As Comment

  Dim iSave As Integer

  Const MaxWidth = 200

  iSave = Application.DisplayCommentIndicator

  Application.DisplayCommentIndicator = xlCommentAndIndicator

  For Each WS In ActiveWorkbook.Worksheets

    For Each C In WS.Comments

      C.Shape.Left = C.Parent.Offset(0, 1).Left + 10

      C.Shape.Top = C.Parent.Offset(0, 1).Top - 10

    Next

  Next

  Application.DisplayCommentIndicator = iSave

End Sub

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk

Answer 2

IS THERE AN EASIER WAY TO DO THIS FOR A PERSON WHO HAS NO IDEA HOW TO DO THIS MACRO THING? PERSON IS ME