Different shape width in different views in Excel 2007??

[Luca Brasi]

In Excel 2007, I create a shape in "Normal" view and set its width to
any value (e.g. to 15cm). Now I change to "Page Layout" view and guess
what... the width now shows up as something more than 16cm??!
(The width property as points in VBA changes accordingly.)

Switching to "Page Break View" will result in one more different width
value.

Is this "just" a big bug in Excel 2007 or did I miss something??

I know that the selected default printer or the chosen zoom factor may
have an influence on the shown size of a shape, but these discrepancies
are not that big (so far as I observed).

Any hints on this issue will be greatly appreciated. Thanks!

 

[Jim Rech]

I see what you mean in that the nominal width of a shape changes slightly
when you switch between views. By nominal width I mean that the width
number that appears in the Drawing Tools contextual tab. By if I size a
shape to fit exactly in a cell and then switch views the shape still fits
exactly in the cell. So aside from the number appearing in the width box in
the ribbon what difference does it make?

--
Jim
| In Excel 2007, I create a shape in "Normal" view and set its width to
| any value (e.g. to 15cm). Now I change to "Page Layout" view and guess
| what... the width now shows up as something more than 16cm??!
| (The width property as points in VBA changes accordingly.)
|
| Switching to "Page Break View" will result in one more different width
| value.
|
| Is this "just" a big bug in Excel 2007 or did I miss something??
|
| I know that the selected default printer or the chosen zoom factor may
| have an influence on the shown size of a shape, but these discrepancies
| are not that big (so far as I observed).
|
| Any hints on this issue will be greatly appreciated. Thanks!

 

[Luca Brasi]

I completely agree that in most cases no Excel user will bother about
this issue.

By if I size a shape to fit exactly in a cell and then switch views
the shape still fits exactly in the cell.

But I don't agree to that statement! The bigger the column widths are,
the bigger the discrepancies between column and shape width are. For
testing, set the width of column A in Normal view to almost the paper
size width, create a shape in this column with the same width and switch
to Page Layout view. On my machine the column A will show as more than
1cm wider than the shape.

In my special case we have a workbook that must comply with corporate
design guidelines. Means that shapes (logo image, text boxes,
rectangles, etc) and column widths must be set according the design
definitions.

Most of this is done in VBA code. After having migrated to 2007 this
code didn't produce the expected result anymore. I now adjusted it so
that the normal view is set before any positioning or sizing of elements
on the sheet is done.

But as a user might switch back to Page Layout view, he might think that
there is something wrong with his sheet and start to adjust size and
position by himself. And by doing so he will just screw it up more...

After some more testing I'm even more confused. It seems to me that it
is very important if you create a shape in Normal or in Page Layout
view. In my tests, the shape behaved very differently.

Now I think it would be best to do all layout actions in the new Page
Layout view and never in Normal view.

 

[Luca Brasi]

Just another short remark:
The conclusion I draw in my last post is definitively wrong!
Whenever you have to exactly layout a worksheet (manually or with VBA)
better do NOT do this in Page Layout view! Always use Normal view as
printing (or the print preview) depends on the Normal view.

 

[Jim Rech]

It definitely is screwy. I inserted a large text that spanned across 3
cells exactly in normal view. When I switch to Page Layout view the width
did not reach all the way across the third cell. So I widened it in Page
Layout view to fit the 3 cells and I expected it to be too width in normal
view. But it wasn't! It still fit exactly in normal view. Go figure.
Aside from the 'width' you see in the Drawing Tools width box being
different in the two views it at least looks okay. And it looked okay in
print preview too.

--
Jim
| Just another short remark:
| The conclusion I draw in my last post is definitively wrong!
| Whenever you have to exactly layout a worksheet (manually or with VBA)
| better do NOT do this in Page Layout view! Always use Normal view as
| printing (or the print preview) depends on the Normal view.
|
|
| Luca Brasi wrote:
| > I completely agree that in most cases no Excel user will bother about
| > this issue.
| >
| > > By if I size a shape to fit exactly in a cell and then switch views
| > > the shape still fits exactly in the cell.
| > But I don't agree to that statement! The bigger the column widths are,
| > the bigger the discrepancies between column and shape width are. For
| > testing, set the width of column A in Normal view to almost the paper
| > size width, create a shape in this column with the same width and switch
| > to Page Layout view. On my machine the column A will show as more than
| > 1cm wider than the shape.
| >
| > In my special case we have a workbook that must comply with corporate
| > design guidelines. Means that shapes (logo image, text boxes,
| > rectangles, etc) and column widths must be set according the design
| > definitions.
| >
| > Most of this is done in VBA code. After having migrated to 2007 this
| > code didn't produce the expected result anymore. I now adjusted it so
| > that the normal view is set before any positioning or sizing of elements
| > on the sheet is done.
| >
| > But as a user might switch back to Page Layout view, he might think that
| > there is something wrong with his sheet and start to adjust size and
| > position by himself. And by doing so he will just screw it up more...
| >
| > After some more testing I'm even more confused. It seems to me that it
| > is very important if you create a shape in Normal or in Page Layout
| > view. In my tests, the shape behaved very differently.
| >
| > Now I think it would be best to do all layout actions in the new Page
| > Layout view and never in Normal view.
| >
| >
| >
| >
| > Jim Rech wrote:
| >> I see what you mean in that the nominal width of a shape changes
| >> slightly when you switch between views. By nominal width I mean that
| >> the width number that appears in the Drawing Tools contextual tab. By
| >> if I size a shape to fit exactly in a cell and then switch views the
| >> shape still fits exactly in the cell. So aside from the number
| >> appearing in the width box in the ribbon what difference does it make?
| >>

 

[Luca Brasi]

It's like in Normal view there's kind of a "hidden space" for shapes
between the columns. If you resize a shape in Page Layout view and don't
make changes bigger than this hidden space, it don't show up in Normal
view (and in print output).
(I don't know if anyone will understand what I'm writing here, I hardly
can understand it by myself...)

And what's more: If you check the length of text in a (rather wide)
cell, the text is shown in Page Layout view like it is printed. But in
Normal view it is much shorter.
Now that would be a reason to use Page Layout view for properly
designing a worksheet...