Custom View Fails with "Can't push objects off the sheet", but there are no objects!

BoredTrevor

New Member
Joined
Oct 11, 2024
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I'm using Microsoft 365 for Enterprise, and I'm running into a weird bug trying to apply Custom Views to my workbook. Was wondering if anyone knew what was going on or how to get more debugging information!

I've got a fairly large spreadsheet, and one of the sheets has a small amount of data (28R x 9C) with no formulae, just text. I have a series of Custom Views in the spreadsheet that allow me to filter it so that only a subset of the cells are visible (this is used by a macro that then can export these views to PDF).

Recently when I try to apply one of these Custom Views, I get the following error:
1728665207933.png


Now, I've had a search around the internet and found a set of possible fixes, but none of the ones I've found seem to have made a difference. In particular, I have tried:

- Checking if there are any objects on the sheet (Home -> Find & Select -> Go To Special -> Objects). There aren't!
- Making sure that the Show All Objects setting (File -> Options -> Advanced -> Display options for this Workbook -> For objects, show: All) is correctly set to All.

I've found that if I create a new Custom View that matches what the old one did, I can get that to apply successfully. However, I have a large number of Custom Views in the workbook and it would be quite time consuming to have to go and rewrite them all (not to mention the risk of introducing errors!). So what I'm hoping is that there's some way to identify the cause of these errors and stop them. Even if it was just additional diagnostic information that might get me what I need. Frustratingly, I haven't found a way to inspect what an existing Custom View is supposed to do apart from trying to apply it (which fails!).

Anyone have any ideas? Thanks in advance for your help :).
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Objects can be cell notes and comments. Did you look at the Selection Pane. Home > Find & Select > Selection Pane. You can unhide all of them and see if they are in columns that you are trying to hide. This usually only happens to me when I'm trying to hide all the columns to the right of the only table on the sheet.
1728667315838.png
 
Upvote 0
Try this code and see if it finds anything else. You need to create a new sheet called AllObjects before running the code. You can use the ShowTopLeftOfCell SUB to find out where each cell is: just select a different cell and run it.

VBA Code:
Sub ListAllObjects()
  Dim ws As Worksheet
  Dim objFSO As Object
  Dim objFile As Object
  Dim Sh As Shape
  Dim R As Range
  Dim X As Long
  
  Application.Calculation = xlCalculationManual
  
  X = 0
  Set R = Sheets("AllObjects").Range("A1")
  R.Offset(0, 0).Value = "Sheet"
  R.Offset(0, 1).Value = "Object Type"
  R.Offset(0, 2).Value = "Object Name"
  R.Offset(0, 3).Value = "Object Left"
  R.Offset(0, 4).Value = "Object Top"
  
  For Each ws In ActiveWorkbook.Sheets
    For Each Sh In ws.Shapes
      X = X + 1
      R.Offset(X, 0) = ws.Name
      R.Offset(X, 1) = TypeName(Sh)
      R.Offset(X, 2) = Sh.Name
      R.Offset(X, 3) = Sh.Left
      R.Offset(X, 4) = Sh.Top
   Next Sh
  Next ws
  
  Application.Calculation = xlCalculationAutomatic
  
End Sub

Sub ShowTopLeftOfCell()
  MsgBox "Top: " & ActiveCell.Top & "  Left: " & ActiveCell.Left
End Sub
 
Upvote 0
Thanks. I tried running this, but it reported no Objects on the sheet I'm trying to run the Custom View on. There are objects elsewhere in the workbook though. Do you know if there's any way to check exactly what a Custom View is doing? That might let me see whether somehow it has included something about one of the other sheets, perhaps?
 
Upvote 0
I think I'm probably going to have to resort to trying to recreate all of the Custom Views - hopefully in doing so I'll avoid doing whatever was done to them before to make them not work!
 
Upvote 0

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

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