Receiving error message "Fixed objects will move" when trying to delete rows
2021-09-16 Problem solving 0 281
When a macro crashed while trying to delete a row from an unprotected worksheet I had to do some testing to figure out why this happend.
Applying some inline error handling (On Error Resume Next / On Error Goto 0) prevented the macro crash, but did not delete the rows as expected.
When I tried to delete the row manually I received the cryptical error message Fixed objects will move, and clicking the OK button to confirm resulted in three more error messages of the same sort.
But eventually the row was actually deleted.
The worksheet had only two visible objects, but it also had 4 comments (the "old-style" yellow pop-up notes).
4 error messages and 4 comments seemed like a plausible coincidence, so I took a closer look at the comments.
And when I used the mouse to point at the cells with the comments I immediately saw that the comments where huge and probably covered all the rows in the worksheet (I didn't scroll all the way down to check).
I used the macro below to reset the size and position for all the comments in the workbook, and the macro that tried to delete rows worked like it should afterwards.
Sub Comments_AutoSizeAndResetPos(wb As Workbook, Optional blnRounded As Boolean = False) ' updated 2018-05-18 by OPE ' apply autosize and reset the top/left position for all comments in a workbook ' example: Comments_AutoSizeAndResetPos ThisWorkbook ' applies autosize and resets the top/left position for all comments in a workbook ' example: Comments_AutoSizeAndResetPos ActiveWorkbook, True ' also applies rounded corners to the comments If wb Is Nothing Then Exit Sub Dim ws As Worksheet, objComment As Comment For Each ws In wb.Worksheets For Each objComment In ws.Comments On Error Resume Next ' in case the worksheet is protected With objComment.Shape .TextFrame.AutoSize = True .Top = .Parent.Top - 7.5 .Left = .Parent.Offset(0, 1).Left + 11.25 If blnRounded Then .AutoShapeType = msoShapeRoundedRectangle Else .AutoShapeType = msoShapeRectangle End If End With objComment.Visible = False On Error GoTo 0 Next objComment Next ws Application.DisplayCommentIndicator = xlCommentIndicatorOnly ' reset property to default End SubTo prevent the problem from happening again I added this to the ThisWorkbook module:
Private Sub Workbook_Open() Comments_AutoSizeAndResetPos ThisWorkbook, True ' applies autosize and resets the top/left position for all comments End Sub