"fixed objects will move" warning

Valeria

unread,

May 5, 2010, 7:59:01 AM5/5/10

to

Dear experts,
I am trying to post this again as I haven't solved my issue yet.
I have a long spreadsheet (3000 rows) with columns where many cells contain
comments. This causes the spreadsheet to give this warning message "fixed
objects will move" whenever I try to autofilter.... and I need to hit escape
hundred times to get rid of this message.
Can you imagine how bad this can be when the users are going to have to use
this and autofilter items in the spredasheet!

Can you please help me? Any hint is precious. I have already tried the hint
from the previous post to use Displayalerts=false triiggered by the
autofilter (I have used the worksheet_calculation event on a subtotal)
however this gets triggered AFTER the messages are dispayed.

Many thanks in advance!
Best regards
--
Valeria

Dave Peterson

unread,

May 5, 2010, 9:19:24 AM5/5/10

to

I've never had any trouble with comments in an autofiltered range.

Maybe you have other shapes that are really tiny (or hidden???) that are causing
the trouble.

Hit F5 (edit|goto)|Special|objects

What gets selected?

Ron de Bruin has lots of code to work with shapes here:
http://www.rondebruin.nl/controlsobjectsworksheet.htm

If you can't clean up those shapes (since they're hidden) and you need help with
the macro, post back.

I'm sure you'll get help.

--

Dave Peterson

zxcv

unread,

May 5, 2010, 9:47:45 AM5/5/10

to

I haven't seen your exact issue but I have seen similar effects with
comments. What you can try is to set the properties for the
comments. Right click on the edge of a comment->Format comment-
>Properties->select Move but don't size with cells.

Sorry but I think you will have to do this for all of your comments.

Valeria

unread,

May 5, 2010, 10:15:02 AM5/5/10

to

Hi Dave,
nothing is found with the procedure you described below "no objects found".
Also,w hen I get rid of all comments in the worksheet, I do not get the
warning any longer....

--
Valeria

"Dave Peterson" wrote:

> .
>

Dave Peterson

unread,

May 5, 2010, 10:58:20 AM5/5/10

to

Maybe the comments got changed (like zxcv suggested).

You could run a macro to do his manual suggestion:

Option Explicit
Sub testme()

Dim CMT As Comment
Dim wks As Worksheet

Set wks = ActiveSheet

For Each CMT In wks.Comments
CMT.Shape.OLEFormat.Object.Placement = xlMoveAndSize
Next CMT

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

--

Dave Peterson

Valeria

unread,

May 5, 2010, 12:02:01 PM5/5/10

to

Now you have really made my day. I was losing sleep on this issue and by
inserting what you said in my code (no I did not do it by hand - over 3000
comments!!!) it worked so beautifully.

sha.Cells(k, 7).Comment.Shape.Placement = xlMove

Many, many thanks!
--
Valeria

"zxcv" wrote:

> .
>