Fixing Excel "Cannot shift objects on sheet" without deleting all your comments

You probably already know by now that "Cannot shift objects on sheet" is probably caused by a misbehaving comment.  It prevents you from hiding columns; in some cases it might also prevent you from inserting columns.

Deleting all your comments is one solution.  If you have a lot of comments, this will not be a preferable solution.

Another solution that is not frequently mentioned is to fix all the comments so that they are set to "move and size with cells".  The misbehaving comment is probably not so set.

It is possible to do this manually.  Obviously with anything more than about 10 comments, this is impractical.  Besides, your comments are probably all over the damn place since they have not been moving with their target cell.

To fix this, you can use "A macro to un-mess Excel comments", as posted at  m2otech.com.  This macro changes all comments to "move and size with cells", plus some other cleanup.

After running the macro, I was able to successfully hide my problematic columns.  Plus my worksheet looked a lot neater.

If you get compile errors in Excel, you may need to reformat the following lines of the macro to exist on a single line (i.e. without carriage returns):

MsgBox ("A total of " & CommentCount & " 
comments in worksheet '" & MySheet.Name & "' of workbook '" 
& MyWorkbook.Name & "'" & Chr(13) & "were repositioned 
and resized.")

Useful options to know when debugging this problem:

In Options/Advanced/Display, you can set comments to always show.  This can be useful.  (You can set it back to "show on hover" later.)

Also in Options/Advanced/Display, you can set "all" objects to show.  This can also be useful.  This option can also be toggled with CTRL-6.

Some people find that just messing with the above two options is enough to fix their issue.  Not so for me.