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

[BoredTrevor]

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:

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 .

 

[Jeffrey Mahoney]

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.

 

[BoredTrevor]

Thanks, I did also have a look there but there's nothing:

 

[Jeffrey Mahoney]

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.

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

 

[BoredTrevor]

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?

 

[BoredTrevor]

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!