Written by Debra Dalgleish from Contextures Blog
Do you sometimes get an Excel error that says “Cannot shift objects off sheet” or “Can’t push objects off the sheet”? Why is Excel complaining, and how can you stop it? I was able to fix the problem with a keyboard shortcut: Ctrl + 6. Maybe that will work for you too.
Video: Cannot Shift Objects
This short video shows the Excel error, Cannot Shift Objects Off Sheet, and how you can try to fix the problem. There are details and written steps below the video.
Video Timeline
- 0:00 Introduction
- 0:44 Open Excel Option Settings
- 1:00 Advanced – Display Options Workbook
- 1:17 For Objects, Show: All
- 1:25 Test the Option Change
- 1:38 Shortcut to Change Option
Excel Error: Cannot Shift Objects
Here’s the Excel error message you might see when you try to apply a filter on a worksheet, or try to insert new rows in a worksheet: Can’t push objects off the sheet
In some versions of Excel, a different error appears: Cannot shift objects off sheet
Try the Simple Fix First
If you’re lucky, a keyboard shortcut will fix the problem in your Excel worksheet. Try this simple fix first, to see if it works.
- Go to the worksheet where you get the Excel error
- Press this keyboard shortcut: Ctrl + 6
- This shortcut turns a workbook display option setting on or off, to hide or show the objects
- Repeat the steps that caused the error message, and see if the problem is fixed.
Change Comment Property
If the keyboard shortcut didn’t get rid of the Excel error for you, the problem might be caused by comments on your worksheet.
Excel can’t move them, because of a setting in the comment properties.
Like other shapes, you can set an object positioning property for comments. There are 3 options for that setting:
- Move and size with cells
- Move but don’t size with cells
- Don’t move or size with cells
If a comment is set to “Don’t move or size with cells”, that Excel error might appear, if you’re filtering or inserting columns/rows.
Macro – List Comment Properties
To find all the comments on your worksheet, and to see their object positioning property, you can use the macro shown below – AllCommentsListProperties.
- You can get macro code in the sample file from the Cannot Shift Objects page on my Contextures site.
- Or, copy the code below, and to add this macro to your workbook, copy it to a regular code module.
The AllCommentsListProperties macro adds a new sheet to the workbook, with a list of comments. The list shows the cell address for each comment, and its object positioning property setting.
Macro to List Comments and Properties
Sub AllCommentsListProperties()
Application.ScreenUpdating = False
Dim rngCmts As Range
Dim c As Range
Dim wsA As Worksheet
Dim wsN As Worksheet
Dim i As Long
Dim lPos As Long
Dim myPos As String
Set wsA = ActiveSheet
On Error Resume Next
Set rngCmts = wsA.Cells _
.SpecialCells(xlCellTypeComments)
On Error GoTo 0
If rngCmts Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If
Set wsN = Worksheets.Add
wsN.Range("A1:B1").Value = _
Array("Address", "Position")
i = 1
For Each c In rngCmts
With wsN
i = i + 1
On Error Resume Next
lPos = c.Comment.Shape.Placement
Select Case lPos
Case 1
myPos = "Move/Size"
Case 2
myPos = "Move Only"
Case 3
myPos = "No Move/Size"
End Select
.Cells(i, 1).Value = c.Address
.Cells(i, 2).Value = myPos
End With
Next c
wsN.Range("A1:B1").EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Change the Comment Properties
After you find all the comments, change each comment’s object positioning property, if it’s currently set to “Don’t move or size with cells”.
If there are lots of comments to change, you can use a macro. There’s a macro for that, named AllCommentsMoveAndSize, in the sample file that you can download.
Or, follow these steps to manually change each comment’s Object Positioning property:
- If the comment isn’t showing, right-click the cell with the comment, and click Show/Hide Comment
- Right-click on the border of the comment, and click Format Comment
- Click the Properties tab
- If you only see a Font tab, click Cancel, and try again
- Be sure to click the comment border, don’t click inside the comment
- For Object Positioning,
- choose Move and size with cells
- OR, choose Move but don’t size with cells
- Click OK, to apply the change
Still Getting Error Message
If you’re still getting an error message, after trying both of these fixes, there are a few more things you can try. These suggestions were in the comments, when I wrote about this problem, way back in 2009.
- Greg found and deleted 4 comments, and that solved the problem in his workbook. To find the comments, he: clicked the Review tab at the top of Excel. Then, in the Comments group, he clicked the Next button.
- In newer versions of Excel, click the down arrow in the Notes group, then select Next Note.
- Judy solved the problem by removing hyperlinks from all her graphics.
- Shane fixed the problem by moving the comments, instead of deleting them.
Get the Sample File
To see an example of this Excel problem, and get more details on how to fix it, go to the Cannot Shift Objects page on my Contextures site.
The zipped Excel file is in xlsm format, and contains macros to change comment properties. Be sure to enable macros when you open the file, if you want to test those macros.
More Comment Pages
Here are a few more pages from my Contextures site, where you’ll find comment tips and macros:
Excel Comment Macros
Excel Comments — Tips
More Comment Macros
Threaded Comment Macros
______________________________
Excel Error Cannot Shift Objects Can’t Push Objects
_____________________________
Excel Error Cannot Shift Objects Can’t Push Objects is a post from Contextures Blog and is not allowed to be copied to other sites