Cannot shift objects off sheet - still

domtrump

Board Regular
Joined
Apr 1, 2010
Messages
245
I've searched this forum regarding this error message (and Google) but I still cannot find a way to resolve the "Cannot shift objects off sheet" error when trying to HIDE columns. I'll try to provide as much info as possible:


  • The workbook is saved in Excel 2003 format, though a mix of 2003 & 2007 users use this file
  • The workbook has comments in just about every column. These comments are a required part of the worksheet
  • Up until last week, hiding columns was not a problem. Nothing has changed in the file (that we can see).
  • The columns contain data up to EZ and a new column is added everyday
  • We frequently unhide all columns to review data and then hide all of the columns up to a couple of days ago. We can no longer do that.
  • If we try to hide about one screen's worth of columns at a time instead of the whole range, it usually (but not always) works. This is very time consuming and impractical.
  • We have tried any suggestions in the MS KB related to this error but to no avail (show ALL objects is already turned on).
  • We've tried deleting all rows and columns outside the used data range but this doesn't work either.
  • Someone suggested to set all the comments to "move and size with cells" but we cannot find this option in Excel 2007.

ANY suggestions with this issue would be helpful. This is a critical spreadsheet for us and being able to hide columns and retain the comments is essential.

Thanks.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
If you display all of the comments, do any of them appear close to the right hand edge of the sheet (i.e. near column IV)?
 
Upvote 0
If you display all of the comments, do any of them appear close to the right hand edge of the sheet (i.e. near column IV)?

They don't stretch that far over, but some are displayed out in the blank space to the right of my data. Most of the comments are VERY far away from the cells that contain them. I'm guessing if we could figure out how to keep the comments adjacent to the cells that contain them, we wouldn't be having this issue.

Any ideas? Tried this code, but it had no effect on the worksheet - the comments were still all over the place:

Sub AutosizeComments()
Dim cmt As Comment
Dim cell As Range
On Error Resume Next
For Each cell In ActiveSheet.UsedRange
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cmt.Shape.TextFrame.AutoSize = True
End If
Next cell
End Sub





Thanks.
 
Upvote 0
Try this:
Code:
Sub ResetCommentLocations()
    Dim cmt As Comment, shp As Shape
    For Each cmt In ActiveSheet.Comments
        Set shp = cmt.Shape
        With cmt.Parent
            shp.Top = .Top
            shp.Left = .Left + .Width + 5
            shp.Placement = xlMoveAndSize
        End With
    Next cmt
End Sub
 
Upvote 0
That worked great! Thank you soo much! One question: Is the .Parent property the cell that the comment is anchored to?

Thanks again!

-Dom



Try this:
Code:
Sub ResetCommentLocations()
    Dim cmt As Comment, shp As Shape
    For Each cmt In ActiveSheet.Comments
        Set shp = cmt.Shape
        With cmt.Parent
            shp.Top = .Top
            shp.Left = .Left + .Width + 5
            shp.Placement = xlMoveAndSize
        End With
    Next cmt
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,503
Messages
6,179,134
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top