How to move a rotated shape to a specific location in excel 2010 using vba

Question

I've written some VBA code that automatically creates a chart. One of the axes on this chart doesn't use normal labels but a graphic. I've stored the graphic as an image and I use the .Copy and .Paste methods to get a copy of this image onto the chart.

Here is where it gets confusing. I need to rotate the image to get it aligned with the axis (using the .rotation property). But when I set the .top and .left properties the shape doesn't end up where I would expect. In fact setting the properties to 0 and 0 doesn't do what I would expect either. I've tried changing the order of the way I set the properties on the image object but it only appears in a different (wrong) location.

I'm sure I'm missing some vital aspect of how VBA/Excel is placing the object relative to what I'm setting the top and left properties to. Basically my goal is to make the image on the left side of the chart with the same width as the plot area's height (since the image is rotated I theorize this will make it the same size).

This code does not work:

Sheets(ImageSheet).Shapes("agreement").Copy
c.Chart.Paste
c.Chart.Shapes(1).Rotation=270
c.Chart.Shapes(1).width = c.Chart.PlotArea.height
c.Chart.shapes(1).left = 5
c.Chart.Shapes(1).top = c.Chart.PlotArea.top

I've also tried this code

c.chart.Shapes(1).top = c.chart.PlotArea.top + c.Chart.PlotArea.height

because I thought maybe it was calculating the "top" as the upper-left corner of the image object when it is not rotated (rotating 270 degrees makes this point in a place where it should align with the bottom of the plot area). But that doesn't do what I expected either.

The image is a skinny rectangle that acts as a label for the axis. The chart will end up being laid out like this: http://imgur.com/NrSXR and the axis label image would be something like this http://imgur.com/08EWU

What am I missing here?

Answer 1

Is it possible for you to align your chart into a position where the shape could rest align/on a cell?

IF YES then here is a suggestion:-

• You could position shape into a cell. Then adjust the size to what you need. And rotate.
• Then change its bring forward property be shown on the Chart.
• Next Group Chart and the Shape

PS: I recorded a macro. However it's best if you could show us what your the exact picture (=how your sheeet/chart/image should look like) of your question.

Answer 2

I ended up rotating and resizing the image before copying and pasting to the chart and then positioning it. I had to use the IncrementLeft and IncrementTop methods rather than setting the left and top properties directly because that did not have the desired effect.

When doing the paste into the chart the object always ended up in the upper left hand-corner so I could increment to the left by the small amount I wanted as a margin I wanted there and increment the top by the value of PlotArea.top to align it with the plot area.

I was also surprised that when creating the copy of my image it retained the "name" i referred to it as when I copied it to the new sheet and chart. This was especially useful for positioning the image once it was on the chart.

I also needed execute this code at the very end of my procedure, after everything else had been positioned and aligned, or when I positioned the data labels for one of my series they wouldn't appear correctly.

Here is the code that I ended up using:

    'make a copy of the label image and refer to it with a variable for convenience
    Sheets(ImageSheet).Shapes("maturity").Copy
    i = Sheets(ImageSheet).Shapes.Count
    Sheets(ImageSheet).Paste
    Dim axisImage As Shape
    Set axisImage = Sheets(ImageSheet).Shapes(i + 1)

    'rotate and resize the image
    With axisImage
        .Rotation = 270
        .width = c.Chart.PlotArea.height
    End With

    'cut and paste the new image to the chart
    axisImage.Cut
    c.Chart.Paste

    'position it in the chart using IncrementTop and IncrementLeft because setting the properties directly does not have the desired effect
    c.Chart.Shapes("maturity").IncrementTop c.Chart.PlotArea.top
    c.Chart.Shapes("maturity").IncrementLeft c.Chart.PlotArea.left - c.Chart.Shapes("maturity").height