Prev: Process all cells in a (user) selection - vba
Next: Autocomplete doesn't function properly
From: Valeria on 5 May 2010 07:59
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
From: Dave Peterson on 5 May 2010 09:19
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.

Valeria wrote:
>
> 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
From: zxcv on 5 May 2010 09:47
On May 5, 7:59 am, Valeria <Vale...(a)discussions.microsoft.com> wrote:
> 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

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.
From: Valeria on 5 May 2010 10:15
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:

> 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.
>
> Valeria wrote:
> >
> > 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
> .
>
From: Dave Peterson on 5 May 2010 10:58
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.)


Valeria wrote:
>
> 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:
>
> > 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.
> >
> > Valeria wrote:
> > >
> > > 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
> > .
> >

--

Dave Peterson
 |  Next  |  Last
Pages: 1 2
Prev: Process all cells in a (user) selection - vba
Next: Autocomplete doesn't function properly