Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape top position in Excel 2007
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape top position in Excel 2007
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 "Chris Bruce <Fr.... @aol.com" wrote in message ... |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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape top position in Excel 2007
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/mic...f6e cf155fdea 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape top position in Excel 2007
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 "Chris Bruce <Fr.... @aol.com" wrote in message ... | 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/mic...f6e cf155fdea | 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape top position in Excel 2007
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Chris Bruce <Fr.... @aol.com" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to add pattern to the shape styles for charts in Excel 2007 | Charts and Charting in Excel | |||
Autosize bug in Excel 2007 shape textframes | Excel Programming | |||
Autosize bug workarounds in Excel 2007 shape textframes | Excel Programming | |||
Position the shape | Excel Programming | |||
shape runtime error 438 excel 2007 | Excel Programming |