Calc - Access comments that have moved off the page

Please not that my understanding of the problem has changed since the original post.

I have a random problem with comment boxes in Calc. My Calc workbook is a medium sized spreadsheet with about 15 sheets. On one of the sheets I created a comment box which was fine. When I went to edit it later the comment box disappeared off the sheet with an arrow pointing to it. I could only get to the comment by scrolling down several hundred rows.

I often do edit the size of the comment, but the sudden displacement of the comment has not been associated with that in the past. The two things that have triggered it were trying to move its position using the mouse by even a few columns or rows. And using the cell context menu to edit a comment that was already showing and the comment flew off the top and bottom of the screen. I do not get a chance to edit/select the text in the box.

*Is there any way to recover the text in the comment box? * I have now managed to find comment by scrolling down far enough and can move the comment back. Having understood that I can use position and size on the context I can confirm that I have never used that dialog in this context before. It is also a much easier way of moving the comment box over several hundred rows by using the mouse.

It does not explain why the comment box occasionally randomly suddenly repositions itself by a large number of rows and columns if you try and edit it or move it.

I have had several instances of this in the past. Sometimes it goes down off the bottom, sometimes it will go off the top. Sometimes if you close the workbook and reopen it the comment will go back into its original position. But go to edit or move the comment box and it will fly off the sheet again.

Normally once a comment box seems to have this behaviour the only way forward is to delete the comment and recreate it. In other words if I do find it and reposition it, then as soon as I say move it again it displace itself by many rows.

In hiding many of the open comment boxes, I had another manifestation of the problem. One of the other comment boxes moved vertically up from M183 (top left of comment box) to F127 and the arrow now went from the comment box at an angle from ~F127 to top left corner of A1, even though the comment belonged to K205. I opened and closed the worksheet and this time the comment stayed in its incorrect position, but the arrow went to K205.

I wonder whether the number of comments open was significant; I had probably about 30 comments shown.

The files are stored .ODS format. The version of Libre Office is 7.0.4.2(x64). I am running it on Windows 10 Home Build 19041, though the problem has been around for a few months, but seems to have got worse recently.

I cannot find the issue online. Is this a known problem?

image description

Your files are stored using which format (.ods or some other format like .xls / .xlsx)?

Your are using which LibreOffice version on which operating system?


Please do **not** use *Add Answer* but **[edit](https://ask.libreoffice.org/t/calc-access-comments-that-have-moved-off-the-page/62061)** your original question to enhance the details of your question and/or to upload sample file(s) (answers are reserved for solutions to a problem on this Q&A site). Thanks in advance …

In .ODS format. The version of Libre Office is 7.0.4.2(x64). I am running it on Windows 10 Home Build 19041, though the problem has been around for a few months, but seems to have got worse.

“When I went to edit it later the comment box disappeared off the sheet …”

What do you mean by “off the sheet”? Couldn’t you get access by scrolling?

"Is there any way to recover the text in the comment box?"  

As long as the annotation (comment) not was deleted, it can be accessed and its size and position can be edited by user code.

You obviously edited the size of the annotation. If you did this via the menu: Are you sure the position wasn’t changed inadvertently?

To respond to Lupp’s question.

No, I could not get to the comment box by scrolling down.

You obviously edited the size of the annotation. I often do edit the size of the comment, but the sudden displacement of the comment has not been associated with that in the past. The two things that have triggered it were trying to move its position using the mouse by even a few columns or rows. And as mentioned before using the cell context menu to edit a comment that was already showing and the comment flew off the top and bottom of the screen. I do not get a chance to edit/select the text in the box.

I have had several instances of this in the past. This is the first time I have lost access to a comment completely. Normally closing and reopening calc would allow me, after some messing around, to copy the text. Normally once a comment box seems to have this behaviour the only way forward is to delete it and recreate it.

I am not an expert in Libreoffice basic, but have used it in the past. I will try and see if I can recover the comment text using basic. Thank you for that suggestion.

I’m not a frequent user of comments. Anyway I never experienced such a weird behaviour. I also didn’t read abot it, and didn’t find a related bug report. Very strange behaviour of any kind is sometimes caused by user profile corruption. See LibreOffice user profile - The Document Foundation Wiki
About a year ago I helped a questioner in a forum to rework all his comments (I prefer “annorations”) by custom code. If you are interested I may get back to that and make something of it for you. Tell me if this is OK for you:
Generally adapt width to text, but with an upper limit of …(70 mm e.g.). Then adapt height to text.
All the comments show upper border adjusted with the respective cell and 10 mm rigth of the cell’s right border.
All sheets at once or only active sheet?

@DavidTigger, can you make a copy of the file, remove all sheets except the problematic one, remove all data from the sheet and post it?

I have updated my question as I did find the comment box, it was just much further down than on previous occasions. As stated in the question,I used the size and position menu to reposition it; the original values for X and Y were consistent with its position. So the question left is how did the position get corrupted.

With the new information, I am not sure that creating a 1 sheet version of the problem will help.

Again as per the updates to the question, I am a heavy user of comments in this particular spreadsheet and had about 30 shown.

If there is anything anybody wants to suggest that I observe/look for next time it happens, please say so.

Due to this question I did some testing with V 7.1.1.1… Changig the heigth of the AnnotationShape using the Positition and Size dialog e.g. I had cases where this corrupted the CaptionPoint to absolute (0, 0). This is an obvious bug. I will probably report it. Meanwhile the “macro” I alread talked of should help, but I did not yet prepare it. I wouldn’t use the dialog till the bug is fixed.

As a workaround to copy the content of the comment that fled away, press Ctrl+Alt+C (seen in the cell context menu), select the content (could be with Ctrl+Shift+End) and copy (Ctrl+C).

You can show the comment next to the cell by selecting the cell and pressing Ctrl+F1. (Source: Function Keys Used in Spreadsheets)

I can’t find a keyboard way to move the comment box.

EDIT: Take note that when you move the comment box with the mouse, as soon as it is off the screen, the rows (or columns) will “run” very fast.

Tested with LibreOffice 7.0.4.2 (x86); OS: Windows 6.1.


Add Answer is reserved for solutions. If you think the answer is not satisfactory, add a comment below, or click edit (below your question) to add more information. Thanks.

Check the mark (Answer markCorrect answer mark) to the left of the answer that solves your question.

The ctrl-alt-C is a partial solution and would be in the first case described. The case I added later when the arrow shoots off the top of the comment box, you can see the comment box but there is no indication as to which cell it belongs to! The comments in the sheet indicate how the value in the cell in the column is derived over time. Unfortunately the comments can be very similar and it is not clear what row and sometines column the comment belongs to.

Thanks for the Ctrl+F1, very useful. I found it is not equivalent to Show/Hide comment and does does not allow you to edit the comment.