"cannot shift objects off sheet" error

[George]

(Excel 2002, 10.2614.2625)
Spreadsheet has ~100x150 (rowsxcols). It uses conditional formatting to
color columns by whether they have a cell that matches that in the same
row of a 'filter' column. To visualize patterns over the whole sheet, I
reduce the column width to 1 (or less). This leaves the content not
readable, but the column colors are visible.

This used to work fine. Recently, when I try to reduce the width, I get
the message "cannot shift objects off sheet".

If I delete all comments, I do not get this error.

If I reduce the number of columns to less than ~80, the error goes away.
However, it doesn't seem to matter _which_ columns I delete - either
left or right, it seems to just be a matter of how many columns are
present.

Eliminating rows does not seem to help, unless I delete almost
everything.

This is not a huge issue; but, I would appreciate it if anyone could
shed some light on what's going on.

Thanks,
G

 

[GS]

(Excel 2002, 10.2614.2625)

Spreadsheet has ~100x150 (rowsxcols). It uses conditional formatting
to color columns by whether they have a cell that matches that in the
same row of a 'filter' column. To visualize patterns over the whole
sheet, I reduce the column width to 1 (or less). This leaves the
content not readable, but the column colors are visible.

This used to work fine. Recently, when I try to reduce the width, I
get the message "cannot shift objects off sheet".

If I delete all comments, I do not get this error.

If I reduce the number of columns to less than ~80, the error goes
away. However, it doesn't seem to matter _which_ columns I delete -
either left or right, it seems to just be a matter of how many
columns are present.

Eliminating rows does not seem to help, unless I delete almost
everything.

This is not a huge issue; but, I would appreciate it if anyone could
shed some light on what's going on.

Thanks,
G

The problem is with the display real estate of the comments. Make sure
their position is within the boundaries of your visible cols and you'll
be fine. This will change if you resize cols and so must be adjusted
accordingly.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[George]

The problem is with the display real estate of the comments. Make sure
their position is within the boundaries of your visible cols and you'll
be fine. This will change if you resize cols and so must be adjusted
accordingly.

If you wouldn't mind elaborating, what does "visible cols" refer to?
This sheet does not have any 'hidden' cols. 'Show comments' is not
enabled for any cells. (ie, comments are only visible on mouse
fly-over.) The cells in question contain zero, one, or two characters.

 

[Claus Busch]

Hi George,

Am Fri, 29 Mar 2013 15:05:29 -0400 schrieb George:

If you wouldn't mind elaborating, what does "visible cols" refer to?
This sheet does not have any 'hidden' cols. 'Show comments' is not
enabled for any cells. (ie, comments are only visible on mouse
fly-over.) The cells in question contain zero, one, or two characters.

please have a look:
http://support.microsoft.com/kb/211769/en-us


Regards
Claus Busch

 

[George]

Hi George,

Am Fri, 29 Mar 2013 15:05:29 -0400 schrieb George:


please have a look:
http://support.microsoft.com/kb/211769/en-us

Just in case anyone stumbles on this thread, I'll report how things
turned out:

AFAIK, the only 'objects' in this worksheet are comments. And, as
noted, if all comments are deleted, the column widths can be shrunk
without any problems.

Per the link, I re-formatted all comments in a group of columns to "move
and size with cell". Over a subset of these modified columns, the
re-size operation still failed. But, if the right-most of these columns
was omitted, the re-size worked.

To see if there was some un-fixed cell in that offending column, I
deleted all its comments. Re-size still failed. Likewise, when all
comments in each of the adjacent columns were _separately_ deleted.
However, if I simultaneously delete all comments in all three of these
columns, the re-size works over the subset in question.

This is beyond my skill set. I will just work around the problem, by
deleting _all_ comments when I want to view the sheet with the reduced
column width.

G

 

[Claus Busch]

Hi George,

Am Thu, 11 Apr 2013 07:41:29 -0400 schrieb George:

Per the link, I re-formatted all comments in a group of columns to "move
and size with cell". Over a subset of these modified columns, the
re-size operation still failed. But, if the right-most of these columns
was omitted, the re-size worked.

just an idea:
Use the input message of the data validation for the right-most three
columns


Regards
Claus Busch

 

[Claus Busch]

Hi George,

Am Thu, 11 Apr 2013 14:10:12 +0200 schrieb Claus Busch:

Use the input message of the data validation for the right-most three
columns

instead of comments


Regards
Claus Busch

 

[George]

Hi George,

Am Thu, 11 Apr 2013 07:41:29 -0400 schrieb George:


just an idea:
Use the input message of the data validation for the right-most three
columns

Thanks. That's a clever; but, the fly-over feature of comments is
better for the way I use this sheet. It's not that big a deal to just
delete all comments when I want to see a 'compressed' view. It's just a
visual thing.

G

 

[GS]

Thanks. That's a clever; but, the fly-over feature of comments is

better for the way I use this sheet. It's not that big a deal to
just delete all comments when I want to see a 'compressed' view.
It's just a visual thing.

G

As I already explained in my previous reply.., the problem is the
*display real estate* used by the rightmost comment. They need to be
resized to fit within the display area. Optionally, you can insert 1
column to the right of your data and camouflage it (fill color) so it's
not noticeable. This will allow your data area comments to display
normally!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion