Error: Cannot Shift Items off Sheet

[AlexanderBB]

This has just started to appear as the workbook loads.
It continues to load and everything seems fine .. until I try to Hide or Unhide something.
Some cols are ok and some bring up the error again.

Is there anything I can do to fix it ?

 

[AlexanderBB]

Sorry Simon, not sure what the pictures means.
I think my issue isn't pasting rows. It's Comments. I've restored 20 of them and so far OK.
Is there a limit to how many you can have (I have 1 per row and 3000 rows) , or the size of them etc?
Guess I can progressively add more until it bombs..

 

[Michael M]

I don't believe there is a limit to the number of comments, but I'm guessing there would be a performance hit if you had a lot of them
AND if they had a lot of info or graphics or pictures in each one !!!

 

[AlexanderBB]

(Still) Cannot shift Object Off Sheet

I have asked this before so hoping to get some more info please, if anyone knows?

The problem are the comments in one column. If I delete them, no problem.
I first thought one could be causing the problem for all (as deleted some fixed it) and pehaps I could track it down, but it's not that simple.

The error can appear when when you hide/unhide columns, but also when the workbook loads. Answering OK sees it finish loading but then
you can never hide or unhide a column.

A lot of google hits talk about changing "Show Objects" Setting in Options but that has no effect.
Thanks for any help. This is a really nasty problem.

 

[Michael M]

Re: (Still) Cannot shift Object Off Sheet

It would pay to keep other posters advised of what you have done so far, so I've added the link back to your previous post

https://www.mrexcel.com/forum/excel...-items-off-sheet-post5294204.html#post5294204

I had a look through some of Mr Excels history on this, and this one popped up.
I note you say changing "Show Objects" didn't fix the issue, but there are a couple of alternative methods you may try here...


https://support.microsoft.com/en-us...ry-to-insert-or-hide-rows-or-columns-in-excel

Can you upload the workbook ?

 

[AlexanderBB]

Re: (Still) Cannot shift Object Off Sheet

Thanks Michael. I may have have found something... I ran Sub Test in that link. After that I could hide the columns ok.
A look at the comments showed some had their widths reduced. Then I realised these were those which otherwise were covering some of the columns I was trying to hide.
Still playing around seeing what gives and proving that conclusively - and what might be the best fix.

 

[Michael M]

Re: (Still) Cannot shift Object Off Sheet

OK....hopefully you get it sorted....the Test Sub could be modified to set the shape dimensions, if that helps !!

 

[Fluff]

Re: (Still) Cannot shift Object Off Sheet

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. (rule 12 here: Forum Rules).

I have merged both threads

 

[i_nth]

Re: (Still) Cannot shift Object Off Sheet

This error occurs when you have a comment that extends beyond the visible cells of a worksheet.
For example, if you have a comment in Z1, it will normally be displayed over the range AA1:AC4, or thereabouts. If you select all the columns from AA to XFD and attempt to hide them, there isn't sufficient room to display the comment. Excel will display the message "Can't push objects off the sheet".
The solution is to either:
- Reposition the comment so that it is displayed over cells that are visible (e.g. W1:Y4).
- Move the comment to a cell where it has room for it to be displayed.
- Leave a few columns unhidden so that there is sufficient room.