Mispositioned Shapes

[ajcarr]

I have some code which positions a shape to align with a cell using the .top property. It does this 4 times on one worksheet. The first paste works correctly but the next three position the cells too far down. The code is the same, the row number is passed into a procedure as a parameter. The .top value for the shape is indeed the same as that for the cell where it needs to be positioned but if I position the shape manually, the .top value is equivalent to that for cells several rows higher.

Any suggestions as to what is happening?

 

[SkipVought]

Hi,

Any suggestions as to what is happening?

What? Do we get a prize for GUESSING what your code looks like?

I'd GUESS that you're NOT using the TopLeftCell property.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ajcarr]

Skip, the code looks like this

Cells(143, 3).Select
ActiveSheet.Paste
With Selection
.ShapeRange.LockAspectRatio = msoTrue
.ShapeRange.Height = Rows(143).RowHeight * 0.8
.ShapeRange.Top = Cells(143, 3).Top + 1
.ShapeRange.Left = Cells(143, 3).Left + 1
End With

Cells(143,3).Top is 2215
.Shaperange.Top is 2216

the location that I want it to be is 2087
Cells(139,3).Top is 2085

 

[SkipVought]

Why do you have a specific cell hard coded in your code???

Since you mention the next 3 postions, do you not want to SELECT a cell and then paste relative to the selected cell? Or am I missing something?

You have NOTHING to indicate that, "the row number is passed into a procedure as a parameter"!!!

Please explain EXACTLY what you want this code to do.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ajcarr]

The 143 was passed into the procedure as a parameter. I just substituted this so that I could get the exact numbers. The shape that I am trying to paste is a logo. The program is used by a number of different operations and each has their own logo. It is taken from a file which contains the logo as a shape in Excel and then pasted into the appropriate places in the workbook - about 20 different locations in several worksheets. Only in this one worksheet which contains 4 different reports does it put it into the wrong location for 3 out of the 4 cells. The rows within the worksheet are of varying heights and some are hidden. The code is intended to position the logo within the confines of the header row for the report. These header rows are 3, 73, 143, 213.

 

[SkipVought]

I cannot help you, unless I can see what you're working with. Yes, it is important to know what arguments are passed. Please include ALL code and ALL relevant information.

Try this...

    Dim shp As Shape
    
    With Cells(143, 3)
        ActiveSheet.Paste
        Set shp = ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
        shp.LockAspectRatio = msoTrue
        shp.Height = .RowHeight * 0.8
        shp.Top = .Top + 1      'this seems stange to me that the TOP and LEFT is just ONE POINT (hardly distinguishable) more than TOP & LEFT
        shp.Left = .Left + 1
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ajcarr]

Skip, many thanks for your help. This is sending me up the wall! Your code picked up the wrong shape because there are quite a number of shapes on some pages.

It looks like an Excel 2007 problem since the positioning varies according to whether the ribbon is displayed. It works OK under Excel 2002. The code that worked in the end is

Sub Position_Logo(X As Integer, Y As Integer)


Cells(X, Y).Select
ActiveSheet.Paste
With Selection.ShapeRange
.LockAspectRatio = msoTrue
.Height = Rows(X).RowHeight * 0.8
.Top = Selection.Top + 1
.Left = Selection.Left + 1
End With

End Sub

I couldn't get it to work when referring directly to the Shape itself but I don't understand why ShapeRange works.

Adding 1 is just to stop the logo overwriting the borders which are around the cell - a minor cosmetic touch.

 

[SkipVought]

Your code picked up the wrong shape because there are quite a number of shapes on some pages.

My code picks up the [/b]most recently added shape[/b] since shapes are added to the Shapes Collection in the order in which they were added to the sheet.

It could be that 2007 has changed something. I do not know.

There are other members who might be able to contribute some insight to this 2007 question.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ajcarr]

Skip, could well be that something has changed in 2007 - I've found quite a number of subtle changes. Even when I selected the Shape by name rather than count, I still had the problem of Excel putting the shape in the wrong place. Your code looks as though it should work OK so there might be a bug in Excel 2007. Thanks again for your help.

 

[DougAJ4]

I tried the code posted by Skip in XL2007, and it seems to work, with ribbon visible or hidden, using a simple rectangular shape.

I know that the way in which arcs are specified changed in 2007. Could that have anything to do with it?

Doug Jenkins

http://newtonexcelbach.wordpress.com/

 

[ajcarr]

I really don't understand what caused this problem. The shapes that are being pasted are just simple rectangular shapes. The positioning seems to be a bit intermittent which may be something to do with hidden rows above the row where the object is being pasted. The code worked fine under Excel 2002 but misplaced objects under 2007. However, it now seems to be working OK.