Receiving error message "Fixed objects will move" when trying to delete rows

 2021-09-16    Problem solving    0    11

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 Sub
To 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