Excel Error Cannot Shift Objects Can’t Push Objects

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

Excel error: Can't push objects off the sheet
Excel error: Can’t push objects off the sheet

In some versions of Excel, a different error appears: Cannot shift objects off sheet

Excel error: Cannot shift objects off sheet
Excel error: 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:

  1. Move and size with cells
  2. Move but don’t size with cells
  3. 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.

new sheet with list of comments
new sheet with list of comments

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 _
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

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:

  1. If the comment isn’t showing, right-click the cell with the comment, and click Show/Hide Comment
  2. Right-click on the border of the comment, and click Format Comment
  3. 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
  4. For Object Positioning,
    • choose Move and size with cells
    • OR, choose Move but don’t size with cells
  5. Click OK, to apply the change
Format Comment dialog box - object positioning
Format Comment dialog box – object positioning

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.

  1. 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.
  2. Judy solved the problem by removing hyperlinks from all her graphics.
  3. 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

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

Original source: https://contexturesblog.com/archives/2021/04/01/excel-error-cannot-shift-objects-cant-push-objects/?utm_source=rss&utm_medium=rss&utm_campaign=excel-error-cannot-shift-objects-cant-push-objects

Leave a Reply

Close Menu