Disappearing Comments

[Barney Rubble]

Hi Everyone, I'm new here, so apologies if I haven't provided sufficient detail, but any help would be greatly appreciated. I have a 6Mb shared excel workbook that 5 users have access to. Users are required to add a comment to each cell that they populate. After saving the spreadsheet and re-opening, random comments have disappeared, while others still remain. Is there anything I can do to fix this problem please?

 

[ranford]

Did you ever find a solution? I have exectly the same problem...

Ranford

 

[Barney Rubble]

Hi Ranford, no I haven't had any luck yet, I've found a few other people with the same issue, but no one seems to have a solution!! If I find anything I'll post it here...

 

[RoryA]

Unfortunately, shared workbooks are extremely unreliable, as you may know, and as a result most people tend to avoid them. This means that not only are the problems with them erratic and hard to fix, but there's also a fairly limited user base to report and help solve the problems. I'm actually a little surprised that comments work at all with shared workbooks given their limitations on manipulating drawing objects, but have you managed to identify any common characteristics? For example, have the cells in question had their values/formatting altered by a user just before they disappear?

 

[ranford]

It's actually being used as a schedule for managing about 80 staff. There are up to 10 people who access it to make changes many times a day. Overall it's pretty effective except for the disappearing comments. Often these people will have it open all day.

As you mention it's when people make the changes but intially the comments appear, then seemingly the next day they disappear. Happy to send a copy if you want to look.

 

[Domski]

Hi,

I have a shared workbook that I developed for recording staff absence within our building. When somebody enters some leave it adds a comment detailing the user ID of the person registering the leave and the date they did it.

I have however encountered a couple of problems with them:

Firstly, some indiviuals are only allowed to enter leave for certain sections so I use an access control system that hides/shows the necessary columns. Occasionally the hiding of the columns falls over with a "Run Time Error 1004: Unable to set the hidden property of the range class". I have only found resetting each comment's placement settings to be a fix for this:

Sub Format_Comments()
Dim s As Shape, n As Integer
On Error Resume Next
For Each s In ActiveSheet.Shapes
n = n + 1
s.Placement = xlMoveAndSize
Application.StatusBar = n
Next
Application.StatusBar = False
End Sub

As you guys have also noted I have the issue of the vanishing comments. As it's not really critical that the information is held I've never looked very hard for or found a solution

The most common advice is "don't use comments in a shared workbook" or "don't use shared workbooks full stop" so I'd be interested if you come up with anything.

Dom

 

[RoryA]

I'm happy to take a look if you like, just PM me and I'll give you my email address. Can't guarantee when I'll have a chance to test it multi-user though.
Do all your users have their own user information entered under Tools->Options in Excel?

 

[RedMamaBug]

Ok i have a solution. The same thing happened to me!!! I am using Micro Off '07...on Windows 7.
Ok first you have to Edit Comment (technically the comment is STILL there...you just have to "show it")
2. Move the cursor over the corner to display it as the arrows in north, south, east, west and then right click
3. you want to Format Comment
4. you should see where the "color" is on the pop up and you want to change the "font" to BLACK, NOT white...or Automatic...you may have to do this more than once to get it to take. You should then click ok and go back to the document/excel. You should see the comment...
Yes it may take awhile if you have more than one to do like I did...but hey...it wasn't lost...it was just hiding.
Hope this helped!

 

[khullah]

Ok i have a solution. The same thing happened to me!!! I am using Micro Off '07...on Windows 7.
Ok first you have to Edit Comment (technically the comment is STILL there...you just have to "show it")
2. Move the cursor over the corner to display it as the arrows in north, south, east, west and then right click
3. you want to Format Comment
4. you should see where the "color" is on the pop up and you want to change the "font" to BLACK, NOT white...or Automatic...you may have to do this more than once to get it to take. You should then click ok and go back to the document/excel. You should see the comment...
Yes it may take awhile if you have more than one to do like I did...but hey...it wasn't lost...it was just hiding.
Hope this helped!

perfect!
Although in my case, the solution itself didn't work (Win 7 Excel 2003). Simply went to menu View / Show comments (the button in the revision toolbar was disabled, but not in the menu).

 

[Phil Bradwell]

I am running Excel 2007 on Windows 11. Comments have disappeared from my Excel spreadsheet, all that appears is an arrow. I emailed the spreadsheet to my Android smartphone and opened it there and Google displayed the missing comments. This was sufficient for me as I my priority was to read the comments. Perhaps this could be a route to a full solution.