Shape top position in Excel 2007

[Chris Bruce]

I have an macro to paste shapes onto a worksheet and position them
relative to particular rows. It works fine until tested on a clients
machine running Vista and Excel 2007 when the vertical screen position
of a shape and the range that it sits on are diverging. The left
property is working as expected.

On my clients system, when a shape is placed over a cell, say B600,
and selected, then this test macro is run the highlight cells appear
several rows below the shape.

When the top property reaches 9000 the discrepancy is approximately
160.

I don't have access to another machine running Excel 2007 and I'm
wondering if this is an Excel 2007 'feature' or is it my clients
machine?

Sub test4()
On Error Resume Next
nn = Selection.Name
Set snn = ActiveSheet.Shapes(nn)
ott = snn.Top
oll = snn.Left
ohh = snn.Height
On Error GoTo 0
crr = 0
For r = 1 To 1000
If Cells(r, 2).Top >= ott Then
crr = r - 1
r = 1000
End If
Next r
If crr = 0 Then Exit Sub

Range(Cells(crr, 1), Cells(crr, 4)).Interior.ColorIndex = 22

ctt = Cells(crr, 2).Top
msg = nn _
& vbCrLf & "row = " & crr _
& vbCrLf & "row top = " & ctt _
& vbCrLf & "shape top = " & ott _
& vbCrLf & "shape height = " & ohh
MsgBox msg
Range(Cells(crr, 1), Cells(crr, 4)).Interior.ColorIndex = xlNone
ActiveSheet.Shapes(nn).Top = ott + 10
End Sub


Chris Bruce

 

[Jim Rech]

The macro worked fine for me, even to row 10000 (after modifying it). Fine
means it formatted the same row the shape was on.

Of course in real life I'd use a faster version:

Cells(Selection.TopLeftCell.Row, 1).Resize(1, 4).Interior.ColorIndex = 22

--
Jim
|I have an macro to paste shapes onto a worksheet and position them
| relative to particular rows. It works fine until tested on a clients
| machine running Vista and Excel 2007 when the vertical screen position
| of a shape and the range that it sits on are diverging. The left
| property is working as expected.
|
| On my clients system, when a shape is placed over a cell, say B600,
| and selected, then this test macro is run the highlight cells appear
| several rows below the shape.
|
| When the top property reaches 9000 the discrepancy is approximately
| 160.
|
| I don't have access to another machine running Excel 2007 and I'm
| wondering if this is an Excel 2007 'feature' or is it my clients
| machine?
|
| Sub test4()
| On Error Resume Next
| nn = Selection.Name
| Set snn = ActiveSheet.Shapes(nn)
| ott = snn.Top
| oll = snn.Left
| ohh = snn.Height
| On Error GoTo 0
| crr = 0
| For r = 1 To 1000
| If Cells(r, 2).Top >= ott Then
| crr = r - 1
| r = 1000
| End If
| Next r
| If crr = 0 Then Exit Sub
|
| Range(Cells(crr, 1), Cells(crr, 4)).Interior.ColorIndex = 22
|
| ctt = Cells(crr, 2).Top
| msg = nn _
| & vbCrLf & "row = " & crr _
| & vbCrLf & "row top = " & ctt _
| & vbCrLf & "shape top = " & ott _
| & vbCrLf & "shape height = " & ohh
| MsgBox msg
| Range(Cells(crr, 1), Cells(crr, 4)).Interior.ColorIndex = xlNone
| ActiveSheet.Shapes(nn).Top = ott + 10
| End Sub
|
|
| Chris Bruce

 

[Chris Bruce]

Jim

Thanks for taking the time to investigate. The test was only designed
to illustrate the problem on my clients computer, please forgive the
inelegant style.

I found a possibly similar problem, regarding picture placement,
posted and replied to by you on March 20
http://groups.google.co.uk/group/mi...ea?lnk=gst&q=cell+top+points#c486f6ecf155fdea
I guess there must be something computer specific going on.

I will have to find another machine running Excel 2007 and Vista and
keep trying.

Regards
Chris Bruce

 

[Jim Rech]

Your issue did sound familiar. Thanks for the reminder. It looks like it's
going to be a not uncommon problem. It's going to be hard to figure out if
I can't reproduce it. Something about video I still suspect.

--
Jim
| Jim
|
| Thanks for taking the time to investigate. The test was only designed
| to illustrate the problem on my clients computer, please forgive the
| inelegant style.
|
| I found a possibly similar problem, regarding picture placement,
| posted and replied to by you on March 20
|
http://groups.google.co.uk/group/mi...ea?lnk=gst&q=cell+top+points#c486f6ecf155fdea
| I guess there must be something computer specific going on.
|
| I will have to find another machine running Excel 2007 and Vista and
| keep trying.
|
| Regards
| Chris Bruce

 

[Jon Peltier]

This issue sounds familiar. I don't recall the details, because it came up
at the end of the 2007 beta or shortly after commercial release, but it had
to do with a slight rounding error in the top position of a shape, which by
the time you got down a couple hundred thousand rows, meant the shape was
offset substantially from the row it was supposed to align with.
Substantially here means a few pixels. If it's the same issue, the official
response was "oh, well..."

- Jon