Microsoft Excel user comment relocation

[Gene]

I have been a full time computer programmer since 1968.
We have a very large Excel spreadsheet that has automotive
sales for 141 countries over 40 years.
When updating the spreadsheet this year and trying to edit
cell comments, most the comments have been moved over
significantly to the right (some by 10 to 12 pages).
I have tried everything I could imagine to move the
comments back to the vicinity of the related cell.
Seems we must move each user comment back several pages to
the left manually.
This is really time intensive.
Anyone know how to correct the user comment relocation?

 

[William]

Gene

You could try a macro along the following lines which will replace all
comments on the existing sheet with new ones. (Back up your file first!)

Sub RewriteComments()
Application.ScreenUpdating = False
Dim c As Range, s As String, r As Range
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments)
For Each c In r
s = c.NoteText
c.ClearComments
c.NoteText s
c.Comment.Visible = False
c.Comment.Shape.TextFrame.AutoSize = True
Next c
Application.ScreenUpdating = True
End Sub


--
XL2002
Regards

William
(e-mail address removed)

| I have been a full time computer programmer since 1968.
| We have a very large Excel spreadsheet that has automotive
| sales for 141 countries over 40 years.
| When updating the spreadsheet this year and trying to edit
| cell comments, most the comments have been moved over
| significantly to the right (some by 10 to 12 pages).
| I have tried everything I could imagine to move the
| comments back to the vicinity of the related cell.
| Seems we must move each user comment back several pages to
| the left manually.
| This is really time intensive.
| Anyone know how to correct the user comment relocation?
|

 

[William]

Gene

You could try a macro along the following lines which will replace all
comments on the existing sheet with new ones. (Back up your file first!)

Sub RewriteComments()
Application.ScreenUpdating = False
Dim c As Range, s As String, r As Range
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments)
For Each c In r
s = c.NoteText
c.ClearComments
c.NoteText s
c.Comment.Visible = False
c.Comment.Shape.TextFrame.AutoSize = True
Next c
Application.ScreenUpdating = True
End Sub


--
XL2002
Regards

William
(e-mail address removed)

| I have been a full time computer programmer since 1968.
| We have a very large Excel spreadsheet that has automotive
| sales for 141 countries over 40 years.
| When updating the spreadsheet this year and trying to edit
| cell comments, most the comments have been moved over
| significantly to the right (some by 10 to 12 pages).
| I have tried everything I could imagine to move the
| comments back to the vicinity of the related cell.
| Seems we must move each user comment back several pages to
| the left manually.
| This is really time intensive.
| Anyone know how to correct the user comment relocation?
|

 

[Bradley Dawson]

Try this:

Sub moveComment()
ActiveCell.Comment.Shape.Left = 20
End Sub

You could loop this through a range or just highlight a cell and press a hot
key. The 20 is the number of points from column A, so you may need to
adjust that.

 

[Dana DeLouis]

Hi Gene. Most people ask about moving the comments, so your question is a
little different.
You didn't mention what version you have. I'm just guessing, but here is a
kb article for Excel XP if it applies. ??

XL2002: Comments Appear in the Wrong Place in Workbook Saved As Web Page
http://support.microsoft.com/default.aspx?scid=kb;en-us;820432

<short copy....>
RESOLUTION
A supported fix is now available from Microsoft, but it is only intended to
correct the problem described in this article.