Excel 2007 and inserted images

[ZipCurs]

Hello,

I input a question yesterday with no responses. My previously functional
macro no longer centers imported JPG pictures in cells properly in 2007.
Limited exploration suggests that the code below places the image 5-10 points
too high, which seems to be enough to put the image "outside" of the cell
during the sorting I need to do.

'Scale correctly
HeightRatio = ActiveSheet.Shapes(ActivePicture).Height / Cells(TargetLine,
6).Height
WidthRatio = ActiveSheet.Shapes(ActivePicture).Width / Cells(TargetLine,
6).Width

If HeightRatio >= WidthRatio Then
ActiveSheet.Shapes(ActivePicture).Select
Selection.ShapeRange.Height = 0.95 *
ActiveSheet.Shapes(ActivePicture).Height / HeightRatio
Else
ActiveSheet.Shapes(ActivePicture).Select
Selection.ShapeRange.Width = 0.95 *
ActiveSheet.Shapes(ActivePicture).Width / WidthRatio
End If

'Position picture correctly
With ActiveSheet.Shapes(ActivePicture)
.Top = Cells(TargetLine, 6).Top + (Cells(TargetLine, 6).Height -
..Height) / 2
.Left = Cells(TargetLine, 6).Left + (Cells(TargetLine, 6).Width -
..Width) / 2
End With

I have come up with a lame solution that involves looking at what cell the
TopLeft and BottomRight are in and moving the image slowly until it changes
to the correct cell and then moving by the appropriate amount to center.

I would love to have a simpler solution to the Excel 2007 problem, and, more
importantly, have a better understanding of why the images are not going
where I want them to go.

Thank you in advance.

 

[ZipCurs]

Jim,

Thanks for looking at this. Did you run it in Excel 2007? I know that the
file and macro itself work great to center the image when run in Excel
2000/2003 and that the images do not move when the file is opened on
different computers with different versions of Excel. It most definately is
putting the file in the wrong spot on my computer with Excel 2007 running. I
have already confirmed that the math in the macro is being calculated
properly and the resulting .Top value is the same in Excel 2000 and 2007, the
images are just in physically different places. The differences are big
enough to mess up the way my macros run. I can incrementally move the image
to the spot I want it to be at, but that seems kind of ridiculous and
dangerous, since I don't know why the image is not in the right place to
being with.

Any thoughts would be welcome.

 

[Jim Rech]

Did you run it in Excel 2007?

You mean, did I create an XLSM in Excel 2003 in some kind of perverted
attempt to drive you crazy? Yes, you caught me!

You mean, did I read your message, go to the trouble of reproducing an
example, and fail to notice you were talking about Excel 2007? Yes, I am an
idiot!

No, just kidding.<g>

I think you have video, graphic issues. Would you click the button on the
file I attached and email it back to me?

Oh, just for grins, if your video DPI setting is not "Normal", that is,
96DPI can you try changing it to that?

--
Jim
| Jim,
|
| Thanks for looking at this. Did you run it in Excel 2007? I know that
the
| file and macro itself work great to center the image when run in Excel
| 2000/2003 and that the images do not move when the file is opened on
| different computers with different versions of Excel. It most definately
is
| putting the file in the wrong spot on my computer with Excel 2007 running.
I
| have already confirmed that the math in the macro is being calculated
| properly and the resulting .Top value is the same in Excel 2000 and 2007,
the
| images are just in physically different places. The differences are big
| enough to mess up the way my macros run. I can incrementally move the
image
| to the spot I want it to be at, but that seems kind of ridiculous and
| dangerous, since I don't know why the image is not in the right place to
| being with.
|
| Any thoughts would be welcome.
|
| "Jim Rech" wrote:
|
| > I've run your code repeatedly and, as far as I can, it centers a picture
| > perfectly in a cell. I've attached my example. After the button is
clicked
| > the picture is centered in cell E12.
| >
| > --
| > Jim
| > | > | Hello,
| > |
| > | I input a question yesterday with no responses. My previously
functional
| > | macro no longer centers imported JPG pictures in cells properly in
2007.
| > | Limited exploration suggests that the code below places the image 5-10
| > points
| > | too high, which seems to be enough to put the image "outside" of the
cell
| > | during the sorting I need to do.
| > |
| > | 'Scale correctly
| > | HeightRatio = ActiveSheet.Shapes(ActivePicture).Height /
Cells(TargetLine,
| > | 6).Height
| > | WidthRatio = ActiveSheet.Shapes(ActivePicture).Width /
Cells(TargetLine,
| > | 6).Width
| > |
| > | If HeightRatio >= WidthRatio Then
| > | ActiveSheet.Shapes(ActivePicture).Select
| > | Selection.ShapeRange.Height = 0.95 *
| > | ActiveSheet.Shapes(ActivePicture).Height / HeightRatio
| > | Else
| > | ActiveSheet.Shapes(ActivePicture).Select
| > | Selection.ShapeRange.Width = 0.95 *
| > | ActiveSheet.Shapes(ActivePicture).Width / WidthRatio
| > | End If
| > |
| > | 'Position picture correctly
| > | With ActiveSheet.Shapes(ActivePicture)
| > | .Top = Cells(TargetLine, 6).Top + (Cells(TargetLine, 6).Height -
| > | .Height) / 2
| > | .Left = Cells(TargetLine, 6).Left + (Cells(TargetLine, 6).Width -
| > | .Width) / 2
| > | End With
| > |
| > | I have come up with a lame solution that involves looking at what cell
the
| > | TopLeft and BottomRight are in and moving the image slowly until it
| > changes
| > | to the correct cell and then moving by the appropriate amount to
center.
| > |
| > | I would love to have a simpler solution to the Excel 2007 problem,
and,
| > more
| > | importantly, have a better understanding of why the images are not
going
| > | where I want them to go.
| > |
| > | Thank you in advance

 

[ZipCurs]

Jim,

I do not know where or how to get your enclosure. I would be happy to try
your test with some guidance.

I was operating in dual screen mode, both set to 96 dpi. Switching to
either screen alone behaves pretty much the same way.

It is worth repeating that images on my Excel 2007 machine appear to be
positioned exactly the same on my Excel 2000 machine. It is the resulting
position after install that is different. e.g If an image inserted on Excel
2007 ends up slightly high, it will look slightly high on the Excel 2000
machine. The positions of the images do not move when viewed on different
machines.

 

[Jim Rech]

I just had a friend who has a dual monitor setup try my attachment in Excel
2007 and it centered perfectly, so that doesn't seem to be a factor. If you
want my attachment you should use Outlook Express or another real
newsreader. Or send me your email address and I'll send it to you. I'm not
sure it will prove much though. Sorry no other ideas...
--
Jim
| Jim,
|
| I do not know where or how to get your enclosure. I would be happy to try
| your test with some guidance.
|
| I was operating in dual screen mode, both set to 96 dpi. Switching to
| either screen alone behaves pretty much the same way.
|
| It is worth repeating that images on my Excel 2007 machine appear to be
| positioned exactly the same on my Excel 2000 machine. It is the resulting
| position after install that is different. e.g If an image inserted on
Excel
| 2007 ends up slightly high, it will look slightly high on the Excel 2000
| machine. The positions of the images do not move when viewed on different
| machines.
|
| "Jim Rech" wrote:
|
| > >>Did you run it in Excel 2007?
| >
| > You mean, did I create an XLSM in Excel 2003 in some kind of perverted
| > attempt to drive you crazy? Yes, you caught me!
| >
| > You mean, did I read your message, go to the trouble of reproducing an
| > example, and fail to notice you were talking about Excel 2007? Yes, I
am an
| > idiot!
| >
| > No, just kidding.<g>
| >
| > I think you have video, graphic issues. Would you click the button on
the
| > file I attached and email it back to me?
| >
| > Oh, just for grins, if your video DPI setting is not "Normal", that is,
| > 96DPI can you try changing it to that?
| >
| > --
| > Jim
| > | > | Jim,
| > |
| > | Thanks for looking at this. Did you run it in Excel 2007? I know
that
| > the
| > | file and macro itself work great to center the image when run in Excel
| > | 2000/2003 and that the images do not move when the file is opened on
| > | different computers with different versions of Excel. It most
definately
| > is
| > | putting the file in the wrong spot on my computer with Excel 2007
running.
| > I
| > | have already confirmed that the math in the macro is being calculated
| > | properly and the resulting .Top value is the same in Excel 2000 and
2007,
| > the
| > | images are just in physically different places. The differences are
big
| > | enough to mess up the way my macros run. I can incrementally move the
| > image
| > | to the spot I want it to be at, but that seems kind of ridiculous and
| > | dangerous, since I don't know why the image is not in the right place
to
| > | being with.
| > |
| > | Any thoughts would be welcome.
| > |
| > | "Jim Rech" wrote:
| > |
| > | > I've run your code repeatedly and, as far as I can, it centers a
picture
| > | > perfectly in a cell. I've attached my example. After the button is
| > clicked
| > | > the picture is centered in cell E12.
| > | >
| > | > --
| > | > Jim
| > | > | > | > | Hello,
| > | > |
| > | > | I input a question yesterday with no responses. My previously
| > functional
| > | > | macro no longer centers imported JPG pictures in cells properly in
| > 2007.
| > | > | Limited exploration suggests that the code below places the image
5-10
| > | > points
| > | > | too high, which seems to be enough to put the image "outside" of
the
| > cell
| > | > | during the sorting I need to do.
| > | > |
| > | > | 'Scale correctly
| > | > | HeightRatio = ActiveSheet.Shapes(ActivePicture).Height /
| > Cells(TargetLine,
| > | > | 6).Height
| > | > | WidthRatio = ActiveSheet.Shapes(ActivePicture).Width /
| > Cells(TargetLine,
| > | > | 6).Width
| > | > |
| > | > | If HeightRatio >= WidthRatio Then
| > | > | ActiveSheet.Shapes(ActivePicture).Select
| > | > | Selection.ShapeRange.Height = 0.95 *
| > | > | ActiveSheet.Shapes(ActivePicture).Height / HeightRatio
| > | > | Else
| > | > | ActiveSheet.Shapes(ActivePicture).Select
| > | > | Selection.ShapeRange.Width = 0.95 *
| > | > | ActiveSheet.Shapes(ActivePicture).Width / WidthRatio
| > | > | End If
| > | > |
| > | > | 'Position picture correctly
| > | > | With ActiveSheet.Shapes(ActivePicture)
| > | > | .Top = Cells(TargetLine, 6).Top + (Cells(TargetLine,
6).Height -
| > | > | .Height) / 2
| > | > | .Left = Cells(TargetLine, 6).Left + (Cells(TargetLine,
6).Width -
| > | > | .Width) / 2
| > | > | End With
| > | > |
| > | > | I have come up with a lame solution that involves looking at what
cell
| > the
| > | > | TopLeft and BottomRight are in and moving the image slowly until
it
| > | > changes
| > | > | to the correct cell and then moving by the appropriate amount to
| > center.
| > | > |
| > | > | I would love to have a simpler solution to the Excel 2007 problem,
| > and,
| > | > more
| > | > | importantly, have a better understanding of why the images are not
| > going
| > | > | where I want them to go.
| > | > |
| > | > | Thank you in advance
| >
| >
| >

 

[ZipCurs]

Jim,

I think I am going the throw in the towel and implement my kludge.
Hopefully I will not be paying for this in the future.

Thanks for taking a look at this very bizarre problem.