vba to change the stretch options (Chart)

blatta

Board Regular
Joined
Sep 7, 2004
Messages
67
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Jonas1

New Member
Joined
Jun 22, 2012
Messages
15
Hi Blatta

The code looks like this:
Code:
    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

Board Regular
Joined
Sep 7, 2004
Messages
67
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

New Member
Joined
Jun 22, 2012
Messages
15
Hi Blatta

is this more what you were looking for?

Code:
    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

Board Regular
Joined
Sep 7, 2004
Messages
67
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top