Can't Edit my Excel Comments

[Paul Calcagno]

Haven't heard from anyone at Microsoft.public.excel.misc so I thought I try
re-posting here instead. Thanks...........Paul C.

I'm running Vista Home Premium SP2, 2 GB RAM, Firefox, Spyware Doctor,
MBAM, SAS and CCleaner, Office 2007.

All of a sudden only one (of many) worksheets inside one particular (xlsx)
Excel file will not allow me to edit the comments in any of the cells
where the comments exist. When I right click the cell and select `Edit
Comment' (which is not greyed out) nothing happens. If I try this on any
other xlsx files I have or any other xls files, comment editing works fine.
I tried the help manual and a reboot to no avail.
I've been using/modifying this file for years. It contains tons of comments
that need updating all the time. Comment editing on most any other
xlsx files, or in any cells on any other worksheet within this file or other
files is allowed just fine.
This file (and some of the other worksheets in it) has links that I
routinely enable. Would this somehow be causing this problem? My other
xlsx and xls files also have links but I have no problem editing comments in
those files. Any help would be greatly appreciated. I determined that I can
edit the comments if I do Show==>Edit==>Hide but I'd like to be able to
right click on any comment like I used to and select Edit to modify the
comments.

Any help would be greatly appreciated.

I thought of doing a reboot and then selecting `Last Known Good
Configuration' but don't want to dump anything I may have downloaded.


Thanks.......Paul C.

 

[Dave Peterson]

I saw your other post and didn't have any solutions.

But it sounds to me like that worksheet has gotten corrupted. If the comments
work in other worksheets in the same workbook or a different workbook, then I
don't think it's excel behaving badly.

I had a worksheet that got corrupted. It contained lots and lots of comments.
I was using them to store the old values when something changed.

The only solution I had was to rebuild the worksheet (not a pretty sight!). And
since my guess was that the great number of comments caused my corruption, I
decided not to use so many in the rebuilt worksheet.

It was a miserable effort to get things to work correctly.

But before you do this, you may want to see if you can remove some of your
comments that are no longer necessary. Maybe that'll help.

If you can't get to the comment menu, maybe you can use a macro:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myRng As Range
Dim myCell As Range

Set wks = Worksheets("Sheet1")

With wks
Set myRng = .Range("A1:C10")
For Each myCell In myRng.Cells
If myCell.Comment Is Nothing Then
'do nothing
Else
myCell.Comment.Delete
End If
Next myCell
End With

End Sub

Change the worksheet name and the address to what you want.

Another option to do first (I just thought of it).

Try repairing your workbook when you open it.
Ctrl-o (the equivalent of file|open in xl2007)
Select your file
but instead of clicking on the open button, click on the arrow on that Open
button. Choose Open and Repair.

(I'm using xl2003, but I think xl2007 works the same way.)

And I'd do all this stuff against a copy of the workbook -- not the original.
You don't want to make matters worse!

And my rule of thumb is to limit the number of objects in my worksheets -- this
includes the comment objects, too.

And I'll use controls from the Forms toolbar instead of the controls from the
control toolbox toolbar. I find these behave much better.

 

[Paul Calcagno]

Hi Dave, thanks for your fine comments.

The macro information you provided is, unfortunately total greek to me. I
did try the repair suggestion but that didn't work. This doesn't bode well
for the `repair' function.

One thing I can do is rebuild the worksheet by copying the commented cells
to another location, and copying and pasting the comments into newly
generated comment boxes. Will take a while.

I can't believe that such a basic error doesn't have a documented fix. Seems
logical that any function that Excel can provide should have a documented
repair action when it stops working. What the hell are these programmers
thinking anyway.

I even tried a restore but to no avail. No surprise there since restore
usually doesn't touch saved files.

Thanks again..............Paul C.

 

[Dave Peterson]

I don't have any other guesses, but if you want to play around with macros (for
fun (????!!!)), here are a couple of links:

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

================
One more thing...

Do you have access to other versions of excel? Maybe you can save a copy as
*.xls and see if the older versions (or xl2010???) can open it correctly????

Sometimes, different versions of excel are more sensitive to different problems.

(Yeah, It's a shot in the dark!)

 

[Paul Calcagno]

Hi Dave.

I do have access to other versions of Excel. At lease I can save the errant
doc as a Office 2003 file and see if the comments are editable. I'm not
adverse to learning about macros so I may give your link suggestions a look
see.

Thanks.............Paul C.

 

[Dave Peterson]

If you find a solution (even rebuilding), please post back.

Hi Dave.

I do have access to other versions of Excel. At lease I can save the errant
doc as a Office 2003 file and see if the comments are editable. I'm not
adverse to learning about macros so I may give your link suggestions a look
see.

Thanks.............Paul C.

 

[Paul Calcagno]

So far I've tried saving the file as an Excel 2003 (xls) file but that still
doesn't enable the `edit comments' command. Nor does saving the file as an
Open Office (calc) file. All that does is eliminate all the comments. I even
ran the Diagnostics package which is supposed to do some kind of a repair,
but nothing happened to make my comments editable.

What has worked so far is to copy sections of the spreadsheet with comments
over a clear space, delete the original comments and then copy and paste the
comment text from the new location back to the old places. Took be about an
hour to do this for all the comments in the workbook. All the new comments
re-entered are not editable with the right click, `edit comments' approach.

Next thing is to take a gander at those macro links you
provided..............Paul C.

 

[Dave Peterson]

One thing that would scare me if I were you is that you could spend hours
getting things right and the next time you open the file, the same problem rears
its ugly head.

Have you thought of any alternatives? Maybe using a cell with the comment in it
-- or a different sheet with all the comments. Then maybe you could use
=vlookup() or =index(match()) to return just the notes that you're interested in
(not as comments, but as values in a different cell).

Good luck with whatever approach you take, though.

So far I've tried saving the file as an Excel 2003 (xls) file but that still
doesn't enable the `edit comments' command. Nor does saving the file as an
Open Office (calc) file. All that does is eliminate all the comments. I even
ran the Diagnostics package which is supposed to do some kind of a repair,
but nothing happened to make my comments editable.

What has worked so far is to copy sections of the spreadsheet with comments
over a clear space, delete the original comments and then copy and paste the
comment text from the new location back to the old places. Took be about an
hour to do this for all the comments in the workbook. All the new comments
re-entered are not editable with the right click, `edit comments' approach.

Next thing is to take a gander at those macro links you
provided..............Paul C.

 

[Paul Calcagno]

Scares me too because there's nothing I can put my finger on that
precipitated this event. I've been using this same file for years. No error
messages ever came up, no strange windows coming up asking me to do
something foreign with this file. Statistically that means this could
happen again, but I guess I'll just have to deal with it. I can always use
`show' comments, edit them and then use `hide' comments. And the
As-***%*$holes at Microsoft don't even have a record of this kind of thing
happening on a product that's been out there for 20 years. Go figure. Guess
that falls under the category of `losing touch with reality'. I even tried
sending MS an e-mail describing the problem, only to get an error message
back saying they couldn't comment on my problem because.............get
this............"we have no record of events like this happening on your
system". Yikes!

It's things like this that drive folks (after a while) to alternatives like
Open Office (and it's free!!)...............Paul C.

 

[dunksunk]

I've got the same problem with my long-running "time-sheet" spreadsheet ~ I have colour coded blocks for being off sick, travelling to sites, etc. with comments that I edit to say why I was off sick or where I was going, etc.
I find if I copy cell with a comment that was entered a while back I can't then edit the comment to change it. Also, some older entries cannot be edited - copied or otherwise.
I was just looking if any one knew how to cure the problem and found this thread.
And literally as I was looking at this thread I was messing about with the right-click options and found a workaround..!!
I simply selected "show/hide comments" on the problem cell and the comment appears ~ then click the comment to edit it then right click and select "hide comment" to get rid of it again.
You still can't right-click and edit the cell once this is done but it saves hours of work re-jiggering the whole sheet.

 

[Brett0769]

I ran into this problem with a recent upgrade to Excel 2010. This is the macro I put in place to correct the problem. It works on the currently selected range. The downside is that you lose the bold on the commenter's name.

Public Sub ReplaceComments()
Dim c As Object
Dim strComment As String
For Each c in Selection.Cells
If Not c.Comment Is Nothing
strComment = c.Comment.Text
c.Comment.Delete
c.AddComment(strComment)
End If
strComment = ""
Next c
Set c = Nothing
End Sub