vba to change the stretch options (Chart)

[blatta]

I am desperately looking for the vba code to change the Stretch options Offset (Left, Right, Top and
Bottom) properties of inserted picture in a chart programatically.

Any idea how to do this in vba ?

Thanks in advance.
Best Regards,
Alain

 

[Jonas1]

Hi Blatta

The code looks like this:

    ActiveSheet.Shapes.Range(Array("Picture 1")).Select
    Selection.ShapeRange.ScaleWidth 1.4716981132, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.5192982456, msoFalse, msoScaleFromTopLeft

Hope this helps.

 

[blatta]

Thanks Jonas.

But I think your example is to change the format of a shape but not within a graph.

May be I need to explain a bit better what are the options I like to access thru vba.

When you right click on a column of a chart then select "Format Data Point" then select "Fill" then select "Picture or Texture fill" you have a section call "Stretch Options - Offsets" where you can enter % for left, right top and bottom. I would like to know the vba code to change these percentages.

Regards,
Alain

 

[Jonas1]

Hi Blatta

is this more what you were looking for?

    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).Points(2).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .PresetTextured msoTexturePapyrus
        .TextureTile = msoTrue
        .TextureOffsetX = 0
        .TextureOffsetY = 0
        .TextureHorizontalScale = 1
        .TextureVerticalScale = 1
        .TextureAlignment = msoTextureTopLeft
    End With

Hope this helps.

 

[blatta]

Hi Jonas,

Thanks for your reply.

I cannot make your code so that it change the column width.

The graph I have the columns amount represent the profit and I am using the offset left and right % to set the column's width based on the volume of sales.

I know how to do this manually but I like to write a macro to have it automated. With the code you provided the coloum width is not changing. I have played with the value for the textureOffsetX, .TextureOffsetY, TextureHorizontalScale and TextureVerticalScale but still the column width stay unchanged.

will it help if I send you a copy of my file ?

Regards,
Alain

 

[Jonas1]

Hi Blatta

I think it should help.

Regards.